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

);