Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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
add comment

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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