How to Ensure Referential Integrity Between Base Types and Exclusive Subtypes in Relational Databases?

How to Implement Referential Integrity in Subtypes

Introduction

In relational database modeling, a subtype represents a specialization of a basetype. Implementing referential integrity between basetypes and subtypes ensures that the data in these tables remains consistent and accurate.

Exclusive Subtypes

With exclusive subtypes, a basetype can have only one subtype row. To enforce this constraint:

1. Create a Discriminator Column: Add a column to the basetype to identify the subtype.
2. Implement a CHECK CONSTRAINT: Use a CHECK CONSTRAINT in the subtype table to ensure that the basetype row exists and has the correct discriminator value.

CREATE TABLE BaseTable (
  BaseTypeId INT PRIMARY KEY,
  Discriminator CHAR(1) CHECK (Discriminator IN ('B', 'C', 'D'))
);

CREATE TABLE SubtypeTable (
  SubtypeTypeId INT PRIMARY KEY,
  FOREIGN KEY (BaseTypeId) REFERENCES BaseTable(BaseTypeId),
  CHECK (
    EXISTS (
      SELECT 1 
      FROM BaseTable 
      WHERE BaseTypeId = SubtypeTable.BaseTypeId 
        AND Discriminator = 'B'
    )
  )

The above is the detailed content of How to Ensure Referential Integrity Between Base Types and Exclusive Subtypes in Relational Databases?. For more information, please follow other related articles on the PHP Chinese website!