0

Given the following structure enter image description here There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2? Initially I thought in a CHECK constraint, but subqueries are not allowed.

  • 2
    Please use real names for your tables and columns. No sane person should use TableX and idX for names. – ypercubeᵀᴹ Apr 25 '13 at 18:00
  • It's an abstract case, I dont see the gain to use concrete names. I mean, how to change Table1 for "Customer" helps to understand the problem? – Apocatastasis Apr 25 '13 at 18:03
1

There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2?

It seems to me like you could just set a foreign key constraint directly to Table4.

alter table Table1
add constraint your_constraint_name
foreign key (id2, id3) references Table4 (id2, id3);

You might have to jump through a hoop or two to prevent a cycle in the constraints.

  • I think the more appropriate is to replace the relationships of the Table1 for one relationship in Table4. – Apocatastasis Apr 25 '13 at 18:37
  • You could well be right. That would be one of the hoops. – Mike Sherrill 'Cat Recall' Apr 25 '13 at 19:02
1

You can add Table4.id4, then you can replace Table1.id2 , Table1.id3 with Table1.id4 which references Table4.

Or you can enforce constraint by creating indexed view.

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.