Skip to content

EX0006 : Identify possible missing Foreign Keys

The rule identifies columns in a SQL Server database that lack foreign key constraints but possess column names and data types suggesting they should reference a primary key in another table.

Ensuring database integrity often requires the use of foreign keys to establish relationships between tables. Columns that are intended to reference primary keys in other tables should have foreign key constraints. However, sometimes these references are implied merely by name or data type patterns but are not enforced in the database schema.

Example of potentially problematic table definitions:

CREATE TABLE SalesOrderDetailPartitioned (
SpecialOfferID INT,
-- Other columns
);
CREATE TABLE SpecialOffer (
SpecialOfferID INT PRIMARY KEY,
-- Other columns
);

In this example, the SpecialOfferID column in the SalesOrderDetailPartitioned table resembles the SpecialOfferID primary key in the SpecialOffer table by name and data type but does not have a foreign key constraint to enforce this relationship. Without these constraints, data integrity issues can arise, such as orphaned records or inconsistent data relationships.

  • Potential for broken data integrity due to missing foreign key constraints.

  • Difficulty in understanding and maintaining database relationships without explicit constraints.

The rule has a ContextOnly scope and is applied only on current server and database schema.

Name Description Default Value
ForeignKeyColumnNamePattern Foreign key column name pattern. {column_name}
ForeignKeyNamePattern Foreign key constraint name pattern. regexp:FK*[A-Z]A-Za-z*]+

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

1 hour per issue.

Design Rules, Explicit Rules, Code Smells

There is no additional info for this rule.

Analysis Rules