Is there any possibility to have two PK on one table in sql server?

Question

I am really surprised that the table having two PK columns.

When I am trying to add PK on another column then those two PK columns changed as normal and only one column changed to PK.

Below is the script it was given

ALTER TABLE [dbo].[ProductionPlaning] ADD  CONSTRAINT [PK_ProductionPlaning] PRIMARY KEY CLUSTERED 
(
    [ProductionPlaningId] ASC,
    [Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

A table can never have more than one PK. However, a PK can consist of several columns. — jarlh, Mar 14 '18 at 07:23
This doesn't have two Primary Keys. It has one primary key formed by two fields. — Paul Karam, Mar 14 '18 at 07:24
Possible duplicate of [Two primary keys specified in MySQL database](https://stackoverflow.com/questions/6754772/two-primary-keys-specified-in-mysql-database) — , Mar 14 '18 at 08:10

Ammar Mohammed · Answer 1 · 2018-03-14T10:28:44.087

4

By definition, a primary key is one or more fields that uniquely identifies a record in a database table. Therefore, you cannot have two separate primary keys for the same table.

If you just want the field to be unique, you simply make it a unique field by adding a unique constraint to it ALTER TABLE YourTableNAme ADD UNIQUE (Date);

If you want the both ProductionPlanningId and Date to be the unique identifiers for your table; you make a composite primary key:

ALTER table TABLE_NAME
 ADD CONSTRAINT [name of your PK, e.g. PK_TableName] PRIMARY KEY CLUSTERED(column1, column2, etc.)

edited Mar 14 '18 at 10:28

answered Mar 14 '18 at 07:50

Ammar Mohammed

267
1
8

3

" a primary key is a field" is contradicted by your later example of a composite primary key. A primary key is *one or more* columns. – Damien_The_Unbeliever Mar 14 '18 at 08:03
By definition a primary key is a combination of one _or more_ columns that uniquely identify a row in a table. A primary key is not limited to a single column. – Mar 14 '18 at 08:04

lucky · Answer 2 · 2018-03-14T08:04:11.017

3

The create script describes the situation.

ALTER TABLE [dbo].[ProductionPlaning] ADD  CONSTRAINT [PK_ProductionPlaning] PRIMARY KEY CLUSTERED 
(
    [ProductionPlaningId] ASC,
    [Date] ASC
)

As you see, the PK is created by two columns (ProductionPlaningId and Date) so there is no multiple different PK in the table. It is not possible.

edited Mar 14 '18 at 08:04

answered Mar 14 '18 at 07:36

lucky

12,734
4
24
46

2

_Columns_, not fields. – jarlh Mar 14 '18 at 07:39

score 1 · Answer 3 · answered Mar 14 '18 at 08:49

1

A table can never have more than one Primary Key. However, a Primary Key can consist of several columns.In you example Primary key is made with more than one column .

answered Mar 14 '18 at 08:49

Anand Singh

210
2
13

score 0 · Answer 4 · answered Mar 14 '18 at 11:19

A primary key is defined as one or more columns with the following properties:

The combination of values is unique.
None of the values is ever NULL.
Only one such set is called "primary".

Other sets are called candidate primary keys. A table can have any number of candidates, and you can even declare them as being NOT NULL and UNIQUE. However, the primary in "primary key" means "one".

By default SQL Server actually sorts the data on the data pages using the primary key (this is called a "clustered" index). The data is only stored once, so it can only be sorted once, further emphasizing the one-ness of the primary key.

That said, your question is really about composite primary keys. These are primary keys that have more than one column. In general, I avoid composite primary keys, preferring a synthetic identity column as a primary key. However, they are definitely allowed and sometimes useful.

Is there any possibility to have two PK on one table in sql server?

4 Answers4