Can a sql server table have two identity columns?

by 
CREATE TABLE [dbo].[Foo](
    [FooId] [int] IDENTITY(1,1) NOT NULL,
    [BarId] [int] IDENTITY(1,1) NOT NULL
)

returns

Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'Foo'. Only one identity column per table is allowed.

So, no, you can’t have two identity columns. You can of course make the primary key not auto increment (identity).

Edit: msdn:CREATE TABLE (Transact-SQL) and CREATE TABLE (SQL Server 2000):

Only one identity column can be created per table.

More Related Contents:

  1. Identity increment is jumping in SQL Server database
  2. Adding an identity to an existing column
  3. Is it possible to write single query on two table which are not connected to each other?
  4. Can I use CASE statement in a JOIN condition?
  5. sql server 2008 management studio not checking the syntax of my query
  6. How to create a table from select query result in SQL Server 2008 [duplicate]
  7. Are there any disadvantages to always using nvarchar(MAX)?
  8. Creating an index on a table variable
  9. OR is not supported with CASE Statement in SQL Server
  10. Parse comma-separated string to make IN List of strings in the Where clause
  11. conditional unique constraint
  12. How do I execute a stored procedure once for each row returned by query?
  13. T-SQL: Selecting Column Based on MAX(Other Column)
  14. Using Excel VBA to run SQL query
  15. How to store image in SQL Server database tables column [duplicate]
  16. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  17. The backend version is not supported to design database diagrams or tables
  18. Benefits Of Using SQL Ordinal Position Notation?
  19. Split string and take last element
  20. Can I set ignore_dup_key on for a primary key?
  21. SQL Server replace, remove all after certain character
  22. Inserting rows into a table with one IDENTITY column only [duplicate]
  23. How do I add a auto_increment primary key in SQL Server database?
  24. How to retrieve field names from temporary table (SQL Server 2008)
  25. When using GETDATE() in many places, is it better to use a variable?
  26. Should I use the CASCADE DELETE rule? [duplicate]
  27. Dynamic order direction
  28. Why is RAND() not producing random numbers?
  29. SQL, How to Concatenate results?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment