Fix Error 8110 “Cannot add multiple PRIMARY KEY constraints to table” in SQL Server

If you’re getting SQL Server error 8110 that reads “Cannot add multiple PRIMARY KEY constraints to table…” it’s because you’re trying to add more than one primary key to a table. In SQL Server, a table cannot contain more than one primary key.

It’s quite possible you were trying to create a composite primary key, and so you might need to fix your syntax. If that’s the case, read on.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE order_items (
  order_id INT PRIMARY KEY, 
  product_id INT PRIMARY KEY,
  quantity   INT
);

Output:

Msg 8110, Level 16, State 0, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'order_items'.

I got this error because I tried to create two primary keys when I really meant to create one composite primary key.

Solution

The solution is nice and straight forward, assuming you want to create a composite primary key. To create a composite primary key, we can change the above code to the following:

CREATE TABLE order_items (
  order_id   INT,
  product_id INT,
  quantity   INT,
  PRIMARY KEY (order_id, product_id)
);

Output:

Commands completed successfully.

Composite primary keys are created at the table level. That means we can’t just define the primary key inline with each column definition, as that results in an error (as we saw with the first example). By moving it to the table level, we can specify each column we want the composite primary key to consist of. In our example, our composite primary key consists of the order_id column and the product_id column.

Of course, it’s quite possible you didn’t want a composite primary key, and you accidentally defined two primary keys instead of one. In that case you’ll need to decide which column should be the primary key and remove the PRIMARY KEY code from the other column/s.