Talking about SQL Server.

Table contains column A and B. For simplicity - no key. Both columns may contain any values. But with a limitation: if column A receives a value which was already used in the table (in column A), column B must have same value as already used (in respected row).



A | B

1 | 1

2 | 2

1 | 1

Not allowed (B differs for same A):

A | B

1 | 2

1 | 1

I can use INSTEAD OF UPDATE or INSTEAD OF INSERT triggers to enforce such logic, but I wonder if it is possible to use check constraints of indexes or some other automated tool for the same goal?

  • 1
    This means that B depends on A. Just put A and B ib another table, with A the unique key. If you try to insert (1,2) in that table the unique key will complain. Then remove B from this table and add a foreign key constratint (a) references new_table (a). You can find the B values by joining to the new reference table. – ypercubeᵀᴹ Oct 1 '16 at 23:06
  • 2
    @ypercubeᵀᴹ, your tip is an absolutely obvious and correct design. Probably it is time for me to stop working for today :) – Dima Oct 1 '16 at 23:10

Your Answer

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

Browse other questions tagged or ask your own question.