MSSQL naming conventions

The best take on this I've seen so far.

Based on a post by Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/76db4119-d040-446e-9ea7-6ba90e017338/naming-conventions-for-constraints?forum=transactsql

Picture a database with a Schema called "In" and another schema called "Out", representing input data and output data respectively. These schema have table names that're the same - for example: each schema has it's own IncomeExpense table. In the code these are written as In.IncomeExpense and Out.IncomeExpense in order to avoid ambiguity.

What are the best options for naming constraints for these tables?

Like other objects, constraints belong to a schema so the constraint name doesn't need to be unique across schemas. Constraints are always referenced by name in the context of the owning table so you don't need to schema-qualify the name in DDL scripts.

I commonly see either column names or integers used to uniqueify constraint names in addition the constraint type and table name. Personally, I don't add column name(s) to the primary key constraint since there may be only one PK per table. OTOH, column names allows the constraint name to be more self-describing. I've also seen some primary key and unique constraint naming conventions add other attributes like "cli" or "nci" to denote clustered/nonclustered.

Below are the constraint naming conventions I use most often.

PK_TableName
FK_TableName_ReferencedTableName[_n]
UQ_TableName_ColumnName[_ColumnName2...]  (or UQ_TableName_n)
CK_TableName_ColumnName (or CK_TableName_n)
DF_TableName_ColumnName

An example implemenation:

CREATE SCHEMA [In];
GO
CREATE SCHEMA [Out];
GO

CREATE TABLE [In].Table1(
	Col1 int NOT NULL 
		CONSTRAINT PK_Table1 PRIMARY KEY
	);
	
CREATE TABLE [Out].Table1(
	Col1 int NOT NULL 
		CONSTRAINT PK_Table1 PRIMARY KEY
	);	

CREATE TABLE [In].Table2(
	Col1 int NOT NULL 
		CONSTRAINT PK_Table2 PRIMARY KEY
	,Col2 int NOT NULL 
		CONSTRAINT UQ_Table2_Col2 UNIQUE
		CONSTRAINT CK_Table2_Col2 CHECK (Col2 > 0)
		CONSTRAINT DF_Table2_Col2 DEFAULT 1
	,Col3 int NOT NULL 
		CONSTRAINT FK_Table2_Table1 FOREIGN KEY(Col3)
		REFERENCES [In].Table1(Col1)
);

CREATE TABLE [Out].Table2(
	Col1 int NOT NULL 
		CONSTRAINT PK_Table2 PRIMARY KEY
	,Col2 int NOT NULL 
		CONSTRAINT UQ_Table2_Col2 UNIQUE
		CONSTRAINT CK_Table2_Col2 CHECK (Col2 > 0)
		CONSTRAINT DF_Table2_Col2 DEFAULT 1
	,Col3 int NOT NULL 
		CONSTRAINT FK_Table2_Table1 FOREIGN KEY(Col3)
		REFERENCES [Out].Table1(Col1)
);

datfinesoul/databases - naming_conventions.md

MSSQL naming conventions

An example implemenation: