{"id":586,"date":"2018-05-31T20:53:39","date_gmt":"2018-05-31T13:53:39","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=586"},"modified":"2020-04-11T20:13:24","modified_gmt":"2020-04-11T13:13:24","slug":"sql-server-primary-key","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-primary-key\/","title":{"rendered":"SQL Server PRIMARY KEY"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server PRIMARY KEY<\/code> constraint to create a primary key for a table.<\/p>\n\n\n\n

Introduction to SQL Server PRIMARY KEY constraint #<\/a><\/h2>\n\n\n\n

A primary key is a column or a group of columns that uniquely identifies each row in a table. You create a primary key for a table by using the PRIMARY KEY<\/code> constraint.<\/p>\n\n\n\n

If the primary key consists of only one column, you can define use PRIMARY KEY<\/code> constraint as a column constraint:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name (\n    pk_column data_type PRIMARY KEY<\/span>,\n    ...\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In case the primary key has two or more columns, you must use the PRIMARY KEY<\/code> constraint as a table constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name (\n    pk_column_1 data_type,\n    pk_column_2 data<\/span> type<\/span>,\n    ...\n    PRIMARY KEY<\/span> (pk_column_1, pk_column_2)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server also automatically creates a unique clustered index (or a non-clustered index if specified as such) when you create a primary key.<\/p>\n\n\n\n
SQL Server PRIMARY KEY constraint examples #<\/a><\/h2>\n\n\n\n
The following example creates a table<\/a> with a primary key that consists of one column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.activities (\n    activity_id INT<\/span> PRIMARY KEY<\/span> IDENTITY<\/span>,\n    activity_name VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n    activity_date DATE<\/span> NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this sales.activities<\/code> table, the activity_id<\/code> column is the primary key column. It means the activity_id<\/code> column contains unique values.<\/p>\n\n\n\n
The following statement creates a new table<\/a> named sales.participants<\/code> whose primary key consists of two columns:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.participants(\n    activity_id int<\/span>,\n    customer_id int<\/span>,\n    PRIMARY KEY<\/span>(activity_id, customer_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the values in either activity_id<\/code> or customer_id<\/code> column can be duplicate, but each combination of values from both columns must be unique.<\/p>\n\n\n\n
Typically, a table always has a primary key defined at the time of creation. However, sometimes, an existing table may not have a primary key defined. In this case, you can add a primary key to the table by using the ALTER TABLE<\/code> statement. Consider the following example:<\/p>\n\n\n\n
CREATE<\/span> TABLE<\/span> sales.events(\n    event_id INT<\/span> NOT<\/span> NULL<\/span>,\n    event_name VARCHAR<\/span>(255<\/span>),\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    duration<\/span> DEC<\/span>(5<\/span>,2<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To make the event_id<\/code> column as the primary key, you use the following ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> sales.events \nADD<\/span> PRIMARY KEY<\/span>(event_id);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that if the sales.events<\/code> table already has data, before promoting the event_id<\/code> column as the primary key, you must ensure that the values in the event_id<\/code> are unique.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server PRIMARY KEY<\/code> constraint to create a primary key for a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The following statement creates a table<\/a> without a primary key:<\/p>\n\n\n
The IDENTITY<\/a><\/code> property is used for the activity_id<\/code> column to automatically generate unique integer values.<\/p>\n\n\n\n
Each table can contain only one primary key. All columns that participate in the primary key must be defined as NOT NULL<\/code>. SQL Server automatically sets the NOT NULL<\/a><\/code> constraint for all the primary key columns if the NOT NULL<\/code> constraint is not specified for these columns.<\/p>\n\n\n\n