It is interesting how sometimes the documentation of simple concepts is not available online. I had received email from one of the reader where he has asked how to create Primary key with a specific name when creating the table itself. He said, he knows the method where he can create the table and then apply the primary key with specific name. The attached code was as follows:
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL)
GO
ALTER TABLE [dbo].[TestTable] ADD CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
([ID] ASC)
GO
He wanted to know if we can create Primary Key as part of the table name as well, and also give it a name at the same time. Though it would look very normal to all experienced developers, it can be still confusing to many. Here is the quick code that functions as the above code in one single statement.
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Pinal Dave, Readers Question, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology