Tell me more ×
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.

I know it's possible to create multiple FK's to different tables using the same column, other than the performance hit caused by having a FK, are there any negative consequences of doing so?

http://sqlfiddle.com/#!3/afb95

CREATE TABLE A (aid int primary key);

CREATE TABLE B (bid int, aid int null);
CREATE UNIQUE NONCLUSTERED INDEX [BIdx_aid] ON [dbo].[B] ( aid ASC) ;
ALTER TABLE b ADD CONSTRAINT FK__B__aid__A__aid FOREIGN KEY (aid) REFERENCES A(aid);

CREATE TABLE C (cid int, aid int null);

ALTER TABLE c ADD CONSTRAINT FK__C__aid__A__aid FOREIGN KEY (aid) REFERENCES A(aid);
ALTER TABLE c ADD CONSTRAINT FK__C__aid__B__aid FOREIGN KEY (aid) REFERENCES B(aid);
share|improve this question
If this is a practical question, then this smells of wrong design. If your database is designed properly, you don't need to do things like this. Post your actual problem - we might help your design your tables. – AlexKuznetsov Dec 15 '12 at 3:33
@AlexKuznetsov: there isn't a practical problem, and adding FK's is just about the limit of what is possible at this stage. The FK from C to A(aid) already exists, so the question is remove it and replace with a FK from C to B(aid) or just add it on top? I don't think it makes a practical difference. – jmoreno Dec 15 '12 at 7:49
I wonder what can be a valid use case for having multiple FKs defined this way. I see that it allows values in C only if the value of aid is in both A and B - but, from a bird's view, seeing the whole picture, the question arises: couldn't you solve this differently (possibly in a better way)? – dezso Dec 15 '12 at 8:26
To be precise, FK to B renders FK to A useless if you have an FK on B to A. – dezso Dec 15 '12 at 8:41
@dezso: Right, once the FK's BA and CB are created CA becomes redundant -- just not sure if that redundancy is a problem. – jmoreno Dec 15 '12 at 8:52
show 6 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.