EX0006 : Identify possible missing Foreign Keys
Introduction
Section titled “Introduction”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
Section titled “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:
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.
How to fix
Section titled “How to fix”The rule has a ContextOnly scope and is applied only on current server and database schema.
Parameters
Section titled “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
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Design Rules, Explicit Rules, Code Smells
Additional Information
Section titled “Additional Information”There is no additional info for this rule.