Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Naming Keys & Constraints

 G'day,

This is pretty much my first blog post - at least on sqlservercentral.com -  so to get started I thought that I'd mention something simple that I like to do.

And that's naming my own constraints, rather than letting SQL SERVER apply some cryptic string for me - however good that is.

Now, I know that a lot of people just accept the SQL defaults when creating tables - either in SSMS or via TSQL - which is, that if you do not name the key / constraint then SQL SERVER will name it for you, and that's fine.

I prefer to name the constraint clearly, so that any message that comes back to me during testing that specifically refers to a constraints by name, such as unique constraint violations, can be easily understood - meaning that tracking down the parent table(s) involved is easier (of course a good naming convention will come in handy here)

I also just find it neater when looking at my constraint \ key names! 

Now, it seems a common practice that the naming of keys / constraints is done in ALTER TABLE statements, however it can also be done in the CREATE TABLE statement - which is where I like to do as much naming of keys \ constraints as I can.

You have to use a specific type of syntax to be able to name the constraints in the CREATE TABLE syntax.

The code below demonstrates how to name keys / constraints using that SYNTAX..

CREATE TABLE Customers
(
	[CustomerID] INT IDENTITY(1,1) NOT NULL ,
	[FirstName] NVARCHAR(50) NOT NULL,
	[MiddleNames] NVARCHAR(100) NOT NULL DEFAULT (''),
	[LastName] NVARCHAR(50) NOT NULL,
	[EmailAddress] NVARCHAR(50) NOT NULL
	CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID ASC),
	CONSTRAINT UQ_Customers_Unique_Name UNIQUE NONCLUSTERED (FirstName , MiddleNames , LastName)
	
);
GO

CREATE TABLE [Orders]
(
	[OrderID] INT IDENTITY(1,1) NOT NULL,
	[CustomerID] INT NOT NULL,
	[ItemName] NVARCHAR(100) NOT NULL
	CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID ASC),
	CONSTRAINT FK_Orders_Customers_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
				ON UPDATE NO ACTION
				ON DELETE NO ACTION
);

Comments

Posted by Ricky Lively on 2 September 2010

I have always preferred to name the pk, fk, and constraints after the table:

Customers_tbl

Customers_tpk_CustomerID

Customers_tfk_Orders_tbl

Customers_tdf_MiddleNames

Customers_tix_FirstName_UN

Leave a Comment

Please register or log in to leave a comment.