Missing Constraint

This smell arises when constraints for a foreign key are missing from a schema definition.

Referential integrity is an essential property of relational databases. Values referenced in a foreign key column must exist in the columns of primary or unique keys of the parent table. It can be easily achieved by defining constraints on foreign keys. However, when such constraints are missing for a foreign key it leads to compromized referential integrity of the database.

Example

CREATE TABLE BugsProducts (
id SERIAL PRIMARY KEY,
bug_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
UNIQUE KEY (bug_id, product_id),
-- No foreign key constraints for bug_id and product_id
};

Reference

B. Karwin, “SQL Antipatterns: Avoiding the Pitfalls of Database Programming”. Pragmatic Bookshelf, 2010.