How to Maintain Referential Integrity with Exclusive and Non-Exclusive Subtypes in SQL?

Subtype Referential Integrity with Constraints

Exclusive Subtypes

• Implement a "Discriminator" column in the base type to indicate which subtype row exists for it.
• Use a SQL CHECK CONSTRAINT to ensure a valid Discriminator range (e.g., for Sensor: IN ("B", "C", "D")).
• The base type's PK ensures uniqueness, and the Discriminator prevents duplicate subtype rows.
• A CHECK CONSTRAINT in the subtype calls a User Defined Function (UDF) to verify the existence of the PK Discriminator in the base type.
• This ensures only a valid subtype exists for each PK.
• Enforce the rule "every base type must have at least one subtype" with transactional code.

Non-Exclusive Subtypes

• No Discriminator is required.
• Existence of a subtype is verified with an existence check on the subtype table.
• The usual PRIMARY KEY, FOREIGN KEY, and Range CHECK CONSTRAINTs adequately support Non-exclusive Subtypes.

Preventing Invalid Discriminator Updates in Exclusive Subtypes (addressed in response to comment)

• Use Open Architecture Standards with ACID SQL Transactions.
• Prohibit direct SQL updates to the database.
• Enforce the insertion of base type subtype in a single Transaction.
• Explicitly DELETE the previous subtype before updating its Discriminator.
• Grant EXEC permission only to user ROLES for executing these Transactions.

This ensures that the Discriminator column is protected from invalid updates, maintaining the database's integrity.

The above is the detailed content of How to Maintain Referential Integrity with Exclusive and Non-Exclusive Subtypes in SQL?. For more information, please follow other related articles on the PHP Chinese website!