SA0071B : Check all Foreign Key Constraints for following specified naming convention

Inconsistent or unclear foreign key constraint naming can lead to confusion and maintenance challenges.

Description

Inconsistent or unclear naming of foreign keys can lead to confusion and errors when managing and maintaining databases. A well-defined naming pattern for foreign keys improves clarity and assists developers in quickly understanding the relationships between tables.

For example:

SQL
1-- Example of a foreign key with unclear naming
2ALTER TABLE Orders
3ADD CONSTRAINT FK_Odrs123 FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId);

This example illustrates a poorly named foreign key. Failing to follow a descriptive naming convention makes it difficult to identify the purpose and relationship of the foreign key, increasing the cognitive load during database modifications or when debugging.

  • Increased difficulty in understanding the database schema, especially for new team members or when revisiting the code after a long time.

  • Higher likelihood of human error during database operations due to ambiguous or non-descriptive foreign key names.

How to fix

Ensure foreign key constraints are named according to a clear and consistent naming convention.

Follow these steps to address the issue:

  1. Identify foreign keys in the database that do not follow a descriptive or consistent naming convention.

  2. Decide on a clear naming convention for foreign keys. A common pattern is FK_TableName_ReferencedTableName. For instance, a foreign key from the Orders table referencing the Customers table might be named FK_Orders_Customers.

  3. Use the sp_rename procedure to rename existing foreign key constraints to follow the new naming convention.

For example:

SQL
1-- Example of renaming a foreign key to follow a convention
2EXEC sp_rename 'Orders.FK_Odrs123', 'FK_Orders_Customers', 'OBJECT';

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
NamePattern

Foreign key constraint name pattern.

regexp:FK_[A-Z][A-Za-z_]+

ColumnsListSeparator

Separator which to be used for separating the columns in the {column_list} placeholder.

_

PairedColumnsSeparator

Separator which to be used for separating parent and referenced columns.

_

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
1ALTER TABLE Person.ContactBackup  
2ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID) ;  
3
4CREATE TABLE  Person.ContactBackup (
5ID int NOT NULL PRIMARY KEY,
6ContactID int NOT NULL CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY REFERENCES Person.Contact (BusinessEntityID),
7BackupID int NOT NULL CONSTRAINT FK_ContactBacup_Backup  REFERENCES [Backup].BackupSet (ID)
8)

Analysis Results

No violations found.

See Also

Other Resources