Appendix J - Enforcing Referential Integrity Between Tables

Since Secure Access version 2.2 it is possible to enforce referential integrity between objects stored in the Secure Access AR_Object table and application data stored in external tables. Referential integrity is a system of rules that ensures relationships between rows in Secure Access AR_Object table and rows in external tables that are valid and that you do not accidentally delete objects from Secure Access database that are in-use (referenced) by other applications.

The reference constraints are stored in the AR_Rerefence table and are checked each time before deleting an object from the Secure Access AR_Object table.

The AR_Reference table contains reference constraints in a form of SQL queries specific for certain Secure Access object type. The query must return depended rows (or only the first one, i.e. TOP 1) found for the given Secure Access object. If the query returns one or more rows then the Secure Access object is evaluated as "in-use" and cannot be deleted. The query may contain %OBJECTID% keywords that will be expanded with the ID of the Secure Access object before executing the query.

Here is an example of a row from AR_Reference table that defines reference constraint between Secure Access Users and table CT_Object from the MetaTree database whose rows refer to the users stored in the Secure Access.

INSERT INTO AR_Reference
(ReferenceAlias,
ReferenceObjectTypeID,
ReferenceApplicationID,
ReferenceNotification,
ReferenceTestQuery)
VALUES
('PortSight.MetaTree.CT_Object',
24 /* Users */,
null /* not applicable */,
'The user is referenced by Object from Meta Tree.',
'SELECT TOP 1 1 FROM PortSight_LS.HelpDesk_MetaTree.dbo.CT_Object WHERE ObjectModifiedBy=%OBJECTID% OR ObjectCreatedBy=%OBJECTID%'
)
GO

The prefix "PortSight_LS" is local name of the linked server and "HelpDesk_MetaTree" is name of the Meta Tree (external) database.

Linked server allows access to distributed, heterogeneous queries against OLE DB data sources. After creating a linked server, this server can then execute distributed queries. You can create linked server either with sp_addlinkedserver stored procedure or with Enterprise Manager.

Columns:

ReferenceAlias ..... Unique alias for the reference.

ReferenceObjectTypeID .... Type of object the reference is defined for (foreign key - see table AR_ObjectType for details).

ReferenceApplicationID ..... ID of the application the reference is defined for (foreign key - see table AR_Object for details)

ReferenceNotification .... Error message to be displayed when this referential integrity is violated.

ReferenceTestQuery .... Query for testing the referential integrity must return depended rows found for the given Secure Access object. The query may contain %OBJECTID% keywords that will be expanded before executing the query against a Secure Access object.