Mysql - Conditional Foreign Key Constraints

Going beyond Foreign Key Constraints

In situations where a foreign key may refer to different tables, traditional constraint mechanisms fall short. Polymorphic associations, harnessed through a supertable, can fill in this gap. This supertable operates as an abstraction containing common fields and being referenced by other tables. Each subtype or content type has its own table, with a foreign key link to the supertable.

Here is an example. Suppose we have an entity ContentHolder that could reference a BlogPost or a UserProfile. Here's how you could set it up:

CREATE TABLE ContentHolder (
    id INT PRIMARY KEY AUTO_INCREMENT, // Let the fun begin, autogeneration in action!
    content_type VARCHAR(255), // Content type, because variety is the spice of life!
    content_id INT
); // The supertable, a.k.a the DJ of this party!

CREATE TABLE BlogPost (
    id INT PRIMARY KEY AUTO_INCREMENT, // Here we go, autogeneration again!
    pseudo_id INT,
    // Fields specific to BlogPost
    FOREIGN KEY (pseudo_id) REFERENCES ContentHolder(id)
); // The BlogPost table, because who doesn't love a good read?

CREATE TABLE UserProfile (
    id INT PRIMARY KEY AUTO_INCREMENT, // Autogeneration is really popular, here it comes again!
    pseudo_id INT,
    // Fields specific to UserProfile
    FOREIGN KEY (pseudo_id) REFERENCES ContentHolder(id)
); // The UserProfile table, everyone's got one, right?


Now, the BlogPost and UserProfile tables maintain their own identities and structures but link back to a central ContentHolder. This maintains referential integrity without limiting foreign key constraints to a single table.

Optimizing Subtype Tables

Subtype tables not only support the implementation of polymorphic associations but also offer a normalized way to manage conditional foreign key constraints.

Introducing Conditional Relationships with Generated Columns

MySQL 5.7 introduced Generated Columns, offering a new tool to create conditional foreign key constraints.

CREATE TABLE parent_table (
    id INT PRIMARY KEY, // Every parent has an ID, even in databases
    type VARCHAR(255), // What type of parent are we talking about?
    // other columns
);

CREATE TABLE child_table (
    id INT PRIMARY KEY, // Even children get their own ID's, it's a brave new world!
    parent_id INT,
    parent_type VARCHAR(255) GENERATED ALWAYS AS (CASE WHEN parent_id IS NOT NULL THEN (SELECT type FROM parent_table WHERE id = parent_id) END) STORED,
    // other columns,
    CONSTRAINT fk_conditional FOREIGN KEY (parent_id, parent_type) REFERENCES parent_table(id, type)
); // Child table, because every child table has a parent, right?


In this example, parent_type is a generated column in child_table which conditionally stores the type value from parent_table based on parent_id. The foreign key constraint then factors in both parent_id and parent_type to ensure data integrity based on the condition.

The Constraints of Constraints

While the mentioned methods provide robust solutions to manage conditional foreign key constraints, be aware of the trade-offs they come with.

Performance considerations

Using Generated Columns and subtype tables can complicate your queries and have an impact on performance. Proper indexing of these columns is necessary, and considering the size and scope of your database when implementing these solutions is crucial. Remember that database systems are designed to handle foreign key constraints efficiently. Using triggers or application-layer checks can introduce overhead that may slow down operations.

Deletion cascade

ON DELETE CASCADE in foreign key definitions can maintain the integrity of your database by automatically deleting related rows. Here's how to do it:

ALTER TABLE BlogPost 
ADD CONSTRAINT fk_blogpost_cascade 
FOREIGN KEY (pseudo_id) 
REFERENCES ContentHolder(id) 
ON DELETE CASCADE; // Auto-cleanup in progress, please stand by...


If a row in ContentHolder gets deleted, related rows in BlogPost will be automatically removed due to the cascade action.

Managing complex conditions

If the conditions get too complex, consider handling the business logic in the application layer or within stored procedures for simplicity and clarity in your database schema.