Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Antipattern: Use Dual-Purpose Foreign Key

Explore the dual-purpose foreign key antipattern used in polymorphic associations in SQL. Understand why this approach complicates data integrity enforcement and how to properly query related tables. Learn about its limitations and practical examples to avoid common pitfalls in database design.

We'll cover the following...

Defining a Polymorphic Association

To make Polymorphic Associations work, we must add an extra string column alongside the foreign key on issue_id. The extra column contains the name of the parent table referenced by the current row. In this example, the new column is called issue_type and contains either Bugs or FeatureRequests corresponding to the names of the two possible parent tables in this association.

MySQL
CREATE TABLE Comments (
  comment_id          SERIAL PRIMARY KEY,
  issue_type          VARCHAR(20), -- "Bugs" or "FeatureRequests"
  issue_id            BIGINT UNSIGNED NOT NULL,
  author              BIGINT UNSIGNED NOT NULL,
  comment_date        DATETIME,
  comment             TEXT,
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

We can see one difference immediately: the ...