How to prevent column b containing the same value as any column a in Oracle?
- by Janek Bogucki
What is a good way to prevent a table with 2 columns, a and b, from having any record where column b is equal to any value in column a? This would be used for a table of corrections like this,
MR -> Mr
Prf. -> Prof.
MRs -> Mrs
I can see how it could be done with a trigger and a subquery assuming no concurrent activity but a more declarative approach would be preferable.
This is an example of what should be prevented,
Wing Commdr. -> Wing Cdr.
Wing Cdr. -> Wing Commander
Ideally the solution would work with concurrent inserts and updates.