3

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 ?

  • 1
    Do you mean that it is permitted to have same IDs on tables A and B? For example, in Table A ID := 7 and in Table B ID := 7 at the same time? – vonPryz Jul 12 '16 at 6:10
  • @vonPryz No, I dont want duplicate contents combing two tables. I mean I dont want to allow ID=7 in table B if ID=7 already exist in Table A. – IT researcher Jul 12 '16 at 6:21
9

You could use a sequence for generating the values for ID fields. The documentation explicitly mentions your use case. The example C on said page has a sample implementation about this.

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.

1

table c

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.