Foreign key referring to primary keys across multiple tables?

sql

database-design

data-integrity

foreign-keys

byAnton Shumikhin·Dec 10, 2024

Implement a polymorphic association to reference multiple tables by adding a ref_id and type column to the child table. Note the streamlined example:

CREATE TABLE polymorphic_ref (
  ref_id INT,
  ref_type VARCHAR(100)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY
  -- other columns
  // Note to self: if this line is commented, who will read my comments? 😅
);

CREATE TABLE electronics (
  electronics_id INT PRIMARY KEY
  -- other columns
  // If electricity goes out, does SQL become sequel? 😂
);

The polymorphic_ref table's ref_id relates to book_id or electronics_id, with ref_type indicating the source table. This method bypasses traditional SQL constraints, so business logic at the application layer is essential for enforcing data integrity.

Creating a unified reference table

Consider making a unified supertype table if you have multiple employee tables (like employees, employees_ce, employees_sn). A unified employees table with a type column can both reduce table redundancy and enforce referential integrity by ensuring all foreign keys point to the same primary key.

Retaining data integrity with polyglot foreign keys

When multiple-table foreign key references are required, consider a pseudokey approach. You can create an auxiliary table that stores these pseudokeys, linking different entities across the tables. This approach allows for trouble-free operations such as CASCADE DELETE. To differentiate subtypes, use a tinyint field as a table identifier, coupled with a unique index.

Tweaking deduction tables for multi-table references

When tables like deductions reference multiple employee subtypes, use a single foreign key column that references the unified employees table. This reduces the table's complexity. For tracking the origin of each entry, include an identification field in the deductions table.

Managing complex database relationships

Unified or separate schema?

Evaluate whether separate subtype tables are necessary in the first place, often a unified table with a type differentiation column can reduce redundancy and simplify your schema.

Including an identification field

In a unified schema, include an identification field in all related tables to maintain clear subtype tracing.

Manual enforcement

If non-enforced foreign keys are used because of multi-table reference, application logic must manually enforce data integrity, including parent row existence verification and appropriate handling of updates and deletions.

Dedicated relationship table

For complex scenarios where direct foreign key constraints are not applicable across multiple tables, consider creating a dedicated relationship table. This table will enforce the integrity normally ensured by the database's foreign key functionality.

explain-codes / Sql / Foreign key referring to primary keys across multiple tables?

Linked

Can a foreign key refer to a primary key in the same table?



Sql ON DELETE CASCADE, Which Way Does the Deletion Occur?



How to add 'ON DELETE CASCADE' in ALTER TABLE statement



Adding named foreign key constraints in a SQL Create statement



How to Alter Constraint

