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

The topic describes the SA0071B analysis rule.

Message

Check all Foreign Key Constraints for following specified naming convention

Description

The rule checks the naming of all foreign keys used in CREATE TABLE and ALTER TABLE statements.

The NamePattern variable can be used to select or configure the desired pattern which will be used to check the object name.

The following placeholders will be replaced in the pattern during the key testing:

{parent_table_name} – Name of the parent table.

{referenced_table_name} – Name of the referenced table.

{parent_columns_list} – List of parent table columns.

{referenced_columns_list} – List of referenced table columns.

{parent_referenced_columns_list} – List of paired parent and referenced columns

Regular expression patterns can be used, but the pattern must be prefixed with ‘regexp:’ string in order to be used as a matching regular expression.

How to fix

Review the constraint name and rename it according to the naming convention.

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
Copy
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

Analysis Rules