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 (http://blog.SQLAuthority.com)

About these ads

26 thoughts on “SQL SERVER – Create Primary Key with Specific Name when Creating Table

  1. 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

  2. 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?

    • 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

  3. 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?

  4. 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

  5. Pingback: SQL SERVER – Update Statistics are Sampled By Default Journey to SQL Authority with Pinal Dave

  6. 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.

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

  8. 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.

  9. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  10. Pingback: SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31 « SQL Server Journey with SQL Authority

  11. 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

  12. 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

  13. Pingback: SQL SERVER – Fix Error: 8111 – Cannot define PRIMARY KEY constraint on nullable column in table – Error: 1750 – Could not create constraint. See previous errors « SQL Server Journey with SQL Authority

  14. 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

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s