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)
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.
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
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?
Something I was looking for today.
Thanks Dave
To the point and very accurate.
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?
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
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.
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)
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.
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
Have you look at the CREATE table examples from SQL Server help file?
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
how to alter primary key column
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
You need to drop the contraint and then alter the datatype
how to set primary key for email.id in my registeration form.pls give me example
code in an image? …..really?….
This is one of the best site for SQL Server
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 )
)
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]
Sure. Makes sense in your situation.
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,