0

I need to set 3 columns (idUser, idLab, idProfile) as identity, but when I change one of them all the others set identity as "No" and only the latest one sets to "Yes".

Already checked its data type and all are set to "int"

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Monica Khoury
  • 17
  • 1
  • 8
  • 4
    You can only have one identity column per table. Why do you need multiple? – HoneyBadger May 03 '19 at 09:06
  • Can't see why you'd need multiple here. They would all have the same value, so duplicating the data is meaningless. What are you actually trying to achieve here? – Thom A May 03 '19 at 09:09
  • 2
    You can only have one Identity column in a table. However, if you want multiple column generating numbers for you in table, you can make use of SQL Server Object [`SEQUENCE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017) to generate values for you. – M.Ali May 03 '19 at 09:09
  • [Foreign keys](https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017) might be what you're looking for, perhaps? – Diado May 03 '19 at 09:10
  • yes they are Foreign keys. So if they are identity in the main tables would they increment here ? – Monica Khoury May 03 '19 at 09:14
  • 1
    Um yes, you're not expected to generate them on both tables and keep them in sync. – Dale K May 03 '19 at 09:28

1 Answers1

0

This is because you can have only one column as an identity column in your table. Please refer Can a sql server table have two identity columns? also.

We really do not need multiple Identity column in a table as we can always get the value of the other two columns from the one Identity column. Here, in this case, idLab and idProfile values same as idUser.

So, if I set idUser to be an identity column, it should suffice for me.

Trupti J
  • 512
  • 1
  • 4
  • 16