SQL Server (2008 and Above): Limit of Unique Key Constraints on a Table

  • Article

Introduction

Today I came upon a question in one of SQL Server Forums in Facebook ( sorry the question wasn't from MSDN) which is as below.

Tell me how many unique keys I can create on a single table because one person today told me that I can create only 999 unique keys in a table..

Indeed, I felt that 999 is limit , but on second thought I was wrong.

Performing the test

The test was run on SQL Server 2008 R2 instance and is valid for 2008 and above versions.
Creating 1000 unique key constraints is possible on a table.

--try below....the below one creates 999 unique (key) constraints.
go
use tempdb
go
create table  t1(c1 int  not null)
go
  
declare @cnt int=1000,@sql varchar(1000)
  
while(@cnt>0)
begin
  
    set @sql='alter table t1 add constraint Uniqu_t1_'+cast(@cnt as  varchar(5))+' unique (c1)'
    exec(@sql)
  
    set @cnt=@cnt-1
  
end
go

You will get below error message on the 1000th execution of the loop,

*"Msg 1910, Level 16, State 1, Line 1

Could not create nonclustered index 'Uniqu_t1_1' because it exceeds the maximum of 999 allowed per table or view.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors."*

The error message says you can't create more than 999 non clustered indexes on a table, it doesn't talk about unique key constraints

----To create additional Unique (key) constraint one has to go with clustered index with unique constraint added to it

alter table  t1
add constraint  CI_t1_c1 unique  clustered  (c1)
  
---use the below query to verify the 1000 unique key constraints count.
 EXEC sys.sp_helpconstraint 't1','nomsg'

The output would be

Creating clustered index by choosing C1 as primary key is also possible. But it creates unique index, not unique key constraint.

This can be verified using the below scripts

alter table  t1
drop constraint  CI_t1_c1
go
alter table  t1
add constraint  pk_t1_c1 primary  key (c1)
  
--this is to verify
EXEC sys.sp_helpconstraint 't1','nomsg'

The output would be

Conclusion

So, 999 is the restriction for the number of non-clustered indexes on a table, not the unique constraints limit :) and a table can have 1000 unique key constraints.

Any suggestions or feedback are welcome :)

See Also