EX0006 : Identify possible missing Foreign Keys

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:

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.

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

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

Design Rules, Explicit Rules, Code Smells

There is no additional info for this rule.

Other Resources