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
);
Tags:

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.