CODEWITHSUNDEEP
sql-server
Dale Fraser
Dale Fraser

Reputation: 4748

Can SQL Server have an identity column based on another column

Can SQL Server have an identity column based on another column?

I have a table design like this

Audit 
AuditId
CompanyId

Everything in the system is audited, and the table can be multi tenant, thus the CompanyId.

In the context of an audit, people like to see a sequential sequence of events and due to the multi tenant nature, the identity will skip numbers like this.

  1. Company1 User logged in
  2. Company1 User logged out
  3. Company1 User logged in
  4. Company9 User logged in
  5. Company1 User logged out
  6. Company9 User logged out

I would ideally keep AuditId as a normal identity but wish to have a secondary identity column that is unique to CompanyId.

Is this possible in SQL Server, or do I need to do it in code?

Upvotes: 1

Views: 109

Answers (2)

Akshey Bhat
Akshey Bhat

Reputation: 8545

SQL Server can't have an identity column based on another column because there can be only one identity column in a table.

Upvotes: 1

Mike
Mike

Reputation: 1667

You could have the companyAuditId populated by an insert trigger. It would be defined as int, but on insert the trigger will 

Create trigger trAuditI for insert 

        UPDATE audit
     SET CompanyAuditId = max(companyAuditId) 

    where companyId
        = Inserted.CompanyId

Upvotes: 0

Related Questions