Is there a way to create a unique index across tables in a SQL Server database? I have two tables, Table A and Table B. In that I have a Column named ID, I want to make these Id columns as unique (combining Table A and B's Ids )
How can I do this ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Use sequences instead of identity columns in the following scenarios:
- The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
Be aware that there are a few catches. For example, sequence values are reusable, are not unique by default and can contain gaps. Pay attention to the Limits part in the docs.
tableAid pk fk table a tableBid pk fk table b
This was before the comment I dont want to allow ID=7 in table B if ID=7 already exist in Table A
You could create a third table C, with a primary (or unique) key defined in it, and create before each row INSERT, UPDATE and DELETE triggers in both of your tables A and B that insert, update or delete their key into table C. You could use table C for foreign keys also, if you need to.