Can a SQL Server Table Have More Than One Identity Column?

Can a SQL Server Table Have Two Identity Columns?

Many database designers encounter the need to have multiple columns in a table that auto-increment, either for primary key or other purposes. However, in SQL Server, this is not directly possible. According to the Transact-SQL reference documentation:

Only one identity column can be created per table.

Example:

Consider the following SQL statement, which attempts to create a table with two identity columns:

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

This statement will fail with the following error:

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

Alternative Solutions:

If you need to have multiple auto-incrementing values in a table, consider the following alternative solutions:

• Composite Primary Key: Create a primary key that combines multiple columns into a single, unique identifier.
• Non-Identity Column: Create a non-identity column and manually increment its value using a trigger or other mechanism.
• Separate Table: Create a separate table to store the auto-incrementing values and reference it from your main table using a foreign key.

While SQL Server does not natively support multiple identity columns, these alternative solutions can provide the functionality you need.

The above is the detailed content of Can a SQL Server Table Have More Than One Identity Column?. For more information, please follow other related articles on the PHP Chinese website!