Setting Up Your System › Setting Up Database Access › Setting Up SQL Access to Non-SQL Databases › Defining Sets as Referential Constraints

Defining Sets as Referential Constraints

Because members of sets usually do not contain foreign keys that reference the owner record, the set specification statement can be used to include the set name in the join criteria for two non-SQL records. For example:

SELECT M.NAME FROM OWNER O, MEMBER M
       WHERE O.NAME='BILL' AND "OWNER-MEMBER"

• The schema compiler SET definition PRIMARY KEY and FOREIGN KEY options can be used to define primary and foreign keys for non-SQL records. Adding foreign keys to set definitions provides several advantages for applications that use SQL and can enhance the compatibility of CA IDMS with tools that build SQL applications.
• Referential integrity is enforced when SQL is used to update non SQL records.
• Standard SQL predicates can be used in join statements.
• Standard JDBC and ODBC foreign key metadata APIs can be used to discover relationships between non-SQL records accessed using SQL.
• The foreign key columns must be elements in the member record. If these elements are added to an existing record, you must restructure the database, populate the foreign key fields, and recompile network applications that reference the member record.
• Network applications that STORE or CONNECT members in the set must update the foreign keys to maintain referential integrity. You can use subschema views that do not include the added fields to minimize changes to programs that do not access the foreign keys.

Note: For more information about modifying set definitions, see the CA IDMS Database Administration Guide.