SQL Server - Max columns per primary key

This blog will teach you that making a primary key column has a limit in SQL-server and what can happen when user will exceed that limit.

SQL Server - Max columns per primary key

The maximum limit for primary key column list is 16.

CREATE DATABASE db_MaxColumnPer
Go

USE [db_MaxColumnPer]

/*=================================

Create 16 columns per primary key

===================================*/

CREATE TABLE MaxColumnPerPrimaryKey16(
[ID1] [int],
[ID2] [int],
[ID3] [int],
[ID4] [int],
[ID5] [int],
[ID6] [int],
[ID7] [int],
[ID8] [int],
[ID9] [int],
[ID10] [int],
[ID11] [int],
[ID12] [int],
[ID13] [int],
[ID14] [int],
[ID15] [int],
[ID16] [int],
[ID17] [int],
[ID18] [int]
CONSTRAINT PK_MaxColumnPerPrimaryKey
PRIMARY KEY(ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ID9,ID10,ID11,ID12,ID13,ID14,ID15,ID16)
)
GO

Result window:

Command(s) completed successfully.

We can create one primary key for 1-16 columns in a table.

If we create a primary key more than 16 columns in a table SQL server returns error.

/*=================================

Create 17 or more columns per primary key

Following error will be occur

===================================*/

CREATE TABLE MaxColumnPerPrimaryKey17(
[ID1] [int],
[ID2] [int],
[ID3] [int],
[ID4] [int],
[ID5] [int],
[ID6] [int],
[ID7] [int],
[ID8] [int],
[ID9] [int],
[ID10] [int],
[ID11] [int],
[ID12] [int],
[ID13] [int],
[ID14] [int],
[ID15] [int],
[ID16] [int],
[ID17] [int],
[ID18] [int]
CONSTRAINT PK_MaxColumnPerPrimaryKey
PRIMARY KEY (ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ID9,ID10,ID11,ID12,ID13,ID14,ID15,ID16,ID17)
)
GO

Error:

Msg 1904, Level 16, State 1, Line 30
The index '' on table 'MaxColumnPerPrimaryKey14' has 17 columns in the key list. The maximum limit for index key column list is 16.
Msg 1750, Level 16, State 0, Line 30
Could not create constraint or index. See previous errors.

Summary

 

In this blog we have seen that an error occurs when Primary key exceeding its limit in SQL-server.