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.

Description

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:

SQL
1CREATE TABLE SalesOrderDetailPartitioned (
2    SpecialOfferID INT,
3    -- Other columns
4);
5
6CREATE TABLE SpecialOffer (
7    SpecialOfferID INT PRIMARY KEY,
8    -- Other columns
9);

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.

Scope

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

Parameters
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_]+

Remarks

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

Effort To Fix
1 hour per issue.
Categories

Design Rules, Explicit Rules, Code Smells

Additional Information

There is no additional info for this rule.

See Also

Other Resources