:::: MENU ::::

Multiple identity columns specified for table %’. Only one identity column per table is allowed

  • Feb 07 / 2012
  • 0
Data Modeling and Database Design, DBA Interview questions, dbDigger, Identity Columns, SQL Server Error messages, T-SQL Interview Questions

Multiple identity columns specified for table %’. Only one identity column per table is allowed

Following error message would be generated if more than one identity column is tried to define in a table.
Multiple identity columns specified for table ‘%’. Only one identity column per table is allowed
SQL Server does not allow to have more than one identity column in a single table. Consider following design for generating error message.

 CREATE TABLE identTest  
 (ID1 SMALLINT IDENTITY(1,1),   
 ID2 SMALLINT IDENTITY(1,1))  
 GO  

There would be hardly any scenario when such a design is required. However such functionality may be achieved by a couple of work around

  • Use trigger to insert values in second column
  • Configure second column as computed column and reflect values from identity column
Consult us to explore the Databases. Contact us