1

I am refactoring one of my database tables and I've come up with an issue.

Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c. I have a uniqueness constraint on the combination of these three columns. Column my.d is a foreign key to an auto-incremented ID column in another table foreign.

I realized that this other table also has a column foreign.a and both column as are complete duplicates, i.e., if you join the tables together on d, my.a will always equal foreign.a. Apparently this is part of the requirements.

I would like to remove column my.a from my table, but that will remove the uniqueness constraint. Is there a way to somehow set up a uniqueness constraint on the combination of foreign.a, my.b, my.c? Or am I better off just leaving the duplicate column in place?

2
  • I don't think there is a way to remove the my.a column and keep the uniqueness constraint. But another thing you can do, is replacing the foreign key my (d) references foreign (d) with my (a,d) references foreign (a,d) so what your know see in the database (both column as are complete duplicates) is guaranteed by the database itself. – ypercubeᵀᴹ May 26 '15 at 1:25
  • Yup, I was definitely going to do that if there was no way to get rid of my.a – Swiftheart May 26 '15 at 2:09
0

You cannot directly create a unique constraint across tables. I say directly because you could come up with some indirect scheme; what comes to my mind would be using triggers to insert into a third table that has the unique constraint. But that's a lot of extra work compared to the obvious solution, which is to keep the column "a" in the "my" table.

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.