SA0070A : Check all Primary Key Constraints in the current database for following specified naming convention

In SQL Server, clear and consistent naming of primary keys is vital for database management and maintenance. Poorly named keys make it difficult to understand relationships or manage changes in the database.

For example:

1-- An example of an inconsistent primary key naming
2ALTER TABLE Orders ADD CONSTRAINT PK_1 PRIMARY KEY (OrderID);

This naming is unclear because it doesn’t convey the table or columns involved, which violates naming conventions and best practices.

• Inconsistent naming leads to confusion and makes database management more difficult.

• Hard to identify and manage primary keys, especially in large databases.

Ensure consistent and clear naming of primary keys in SQL Server databases to facilitate effective database management and maintenance.

Follow these steps to address the issue:

1. Identify primary keys with inconsistent names. You can query the system catalog to list all constraint names: SELECT name, object_id FROM sys.objects WHERE type_desc = ‘PRIMARY_KEY_CONSTRAINT’;.

2. Review the current naming conventions for your database. A good convention includes the table name and the column name(s) involved in the primary key. For example, use a format like PK_TableName_ColumnName.

3. Rename the primary keys to adhere to the naming convention. Use sp_rename to change the constraint name. Ensure that the new names uniquely identify the related table and columns.

For example:

1-- Example of renaming a primary key to adhere to the naming convention
2EXEC sp_rename 'PK_1', 'PK_Orders_OrderID', 'OBJECT';

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.

Naming Rules, Code Smells

There is no additional info for this rule.

Other Resources