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