SQL SERVER – Create Primary Key with Specific Name when Creating Table

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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Find Max Worker Count using DMV – 32 Bit and 64 Bit
Next Post
SQL SERVER – Update Statistics are Sampled By Default

Related Posts

27 Comments. Leave new

  • Did you tell the reader that this is in Books Online?

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017

    Example “F” even has a CREATE TABLE example where the primary key is named.

    Reply
  • Hi Pinal… with first look on your query to create the primary key with specific name, I smelled syntax problem (Even though I was certain that you must had tested the query before posting) as we always use to have comma (,) before CONSTRAINT Keyword.

    Wondered that both works :) – check below query

    CREATE TABLE [dbo].[TestTable1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](100) NULL,
    CONSTRAINT [PK_TestTable1] PRIMARY KEY CLUSTERED
    ([ID] ASC)
    )

    In case only one column is candidate for primary key there is another way to have constraint with name in Create table statement (as below)…

    CREATE TABLE [dbo].[TestTable2](
    [ID] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_TestTable2] PRIMARY KEY CLUSTERED,
    [FirstName] [varchar](100) NULL
    )

    Just to have all possibilities together.

    Regards,
    Sajid

    Reply
  • If you script a table in SSMS with default scripting options it includes the script to create the PK as per your 2nd example above.

    Is this really blog worthy?

    Reply
    • Hi JamWheel,

      Very good question. I think book online and SSMS can do lots of things.

      It is about knowing various details. Knowledge various from people to people.

      This blog post is for those who do not know the details.

      Again, this is just a my opinion.

      Kind Regards,
      Pinal

      Reply
  • Something I was looking for today.

    Thanks Dave

    Reply
  • To the point and very accurate.

    Reply
  • Hi,

    This is one of the new and interesting thing for me.

    Again I do have one question. Is there any option to put custom name for constraint while creating a table from the design mode?

    Reply
  • Nutan,

    Yes.. there is an option through SSMS as well.

    In design mode, right click on Blank area, choose “Index/Keys” option from shortcut menus…Select the Index or Primary key in the list that you want to specify the name for… right side of the dialog you will see the option to set the name for it.

    Once changed, close the dialog and save the changes… thats it.

    Regards,
    Sajid

    Reply
  • Deepak Sharma
    April 23, 2010 5:48 pm

    Dear Mr. Pinal,
    I have a question which is not directly related with this topic, but there is some relationship with Primary key and Clustered Index. The question is:

    I have a table “TestIndex”:

    CREATE TABLE TestIndex
    (
    ID INT IDENTITY,
    Name Nvarchar(100)
    )
    CREATE UNIQUE CLUSTERED INDEX MyIndex ON TestIndex(ID)

    Now I run the following query:

    SELECT * FROM sys.indexes WHERE object_id=object_id(‘TestIndex’)

    It will give me the information of Indexes on the table “TestIndex”. Now I write the following query to Add a Primary Key Constraint on the table “TestIndex” on Column ID:

    ALTER TABLE TestIndex
    ADD CONSTRAINT MyKeyClustered PRIMARY KEY (ID)

    Again When I run this quey:

    SELECT * FROM sys.indexes WHERE object_id=object_id(‘TestIndex’)

    It will create a NonClustered Index Named: MyKeyClustered, and
    EXECUTE sp_help TestIndex
    will give the information Primary key created on ID on NonClustered Index.
    My question is this I don’t want to create a NonClustered Index on my Primary Key Constraint, but I want to use the Clustered Index “MyIndex” on Primary Key Constraint. How I can do it?
    Please help.

    Reply
  • Creating a foreign key is very similar.

    Instead of:
    constraint [PK_PersonID] primary key (PersonID)

    Use:
    constraint [FK_Person_PersonType] foreign key (PersonTypeID) references PersonType(PersonTypeID)

    Reply
  • Hi All,
    I am new to SQL Server and is working on a test task to create a database with related tables.
    I have two tables –
    1. First table has the Plant Names with their locations and addresses.
    2. Second table has the performance metrics recorded every month. This means that the second table has repeating Plant Name (every month), RefYear (also repeating values) and RefMonth (also repetitive). The only criteria that each row is different from another is that Plant Name, RefYear and RefMonth are not identical for any two rows.

    Do I have to assign a Primary Key for the second table? The Plant Name can be a Foreign Key relating to the Primary Key of the first table? In Access, the second table has the additional column of Primary Key which is an Autonumber.
    I can add an additional column in the second table in the same lines as Access, but it does not relate to anything in the second table or other tables (e.g. Prime Mover type, Fuel etc) which I propose to add later.
    Can I leave the second table without a Primary Key? Is it a good design? What are the alternatives?
    I’d appreciate for any suggestions.

    Reply
  • sir,
    i am a b.tech student and i want know to know how to set the primary key and foregin key when creating a table for doing my database lab.i read all the statements here,but we are creating the table using the query-create table table name (name varchar(10),………………………);-this may be a simple query for u..but please help me to insert the keys while creating tables….reply me sir

    Reply
  • sorry but I have a problem with a create. Could you help me?

    the query is:

    CREATE TABLE Restaurante
    (
    id INTEGER IDENTITY(1,1) PRIMARY KEY,
    nombre VARCHAR(60) NOT NULL,
    direccion VARCHAR(100) NOT NULL,
    coord_X FLOAT,
    coord_Y FLOAT,
    puntuacion FLOAT
    );

    I think that it’s ok, but I have this mistake: ORA-00907: missing right parenthesis

    How I can solve it?

    thantk

    Reply
  • how to alter primary key column

    Reply
  • i hav a table
    CREATE TABLE Table1(
    Col1 INT NOT NULL,
    Col2 VARCHAR(100)
    CONSTRAINT PK_Table1_Col1 PRIMARY KEY (Col1))
    once table created i need to change id int to bigint how its possible

    Reply
  • how to set primary key for email.id in my registeration form.pls give me example

    Reply
  • code in an image? …..really?….

    Reply
  • This is one of the best site for SQL Server

    Reply
  • How do i store 2 employees that have the same name without sacrificing the usage of the primary key index seek for searching by name in the following database.
    CREATE TABLE dbo.employees
    (
    uniqueEmployeesId int NOT NULL IDENTITY (1, 1),
    name varchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    CONSTRAINT PK_employees PRIMARY KEY CLUSTERED ( name )
    )

    Reply
  • Michael Gibson
    March 30, 2017 8:33 am

    Ok, here is a tricky one: Say you have a Table1 with a primary key PK_Table_1; You also have a Table1_Stage which will eventually replace Table1 with a rename….assume this process has to happen over and over again. How do you create a primary key constraint with a unique name (other than creating a string and executing it)?

    For instance something like the following:

    DECLARE @Rand int
    SELECT @RAND = ROUND(((999 – 1 -1) * RAND() + 1), 0)

    CREATE TABLE [dbo].[Table1_Staging](
    [SomeColumn] [char](25) NOT NULL),
    CONSTRAINT [PK_TABLE1_’ + CAST(@RAND as varchar)] PRIMARY KEY CLUSTERED
    (
    [SomeColumn] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Reply
  • Rakesh Reddy Balireddy
    April 7, 2020 6:56 pm

    Hi Sir,

    I used the below query,

    Use master;
    create ASYMMETRIC KEY Asym_Key_Name
    FROM Provider SGAPTMSDVRP
    WITH PROVIDER_KEY_NAME = ‘Provider_Key_Name’,
    CREATION_DISPOSITION=OPEN_EXISTING

    However, We have two Provider_key_Name inside HSM. How to know which provider_key_name we used in sql ?

    Thank you,

    Reply

Leave a Reply

Menu