Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2002-02-22 : 11:36:42
|
| Ok, so browsing BOL and such and I noticed that you can do this but I couldn't find any info on why you would do this or how this works etc... FOREIGN KEY (xcolumn) REFERENCES xtable(xcolumnA, xcolumB, etc..) has anyone used this or could someone explain this to me :-p- Onamuji |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-02-22 : 13:07:27
|
| Can you post the actual excerpt from BOL that shows this? I don't think that's right. Such a constraint would never be satisfied, unless the fields in the referenced table were equal.Are you sure it wasn't showing a composite foreign key like this: FOREIGN KEY(xcolumnA, xcolumnB) REFERENCES REFERENCES xtable(xcolumnA, xcolumB) where the number of referencing cols matches the number of referenced cols? |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-22 : 13:10:34
|
| FOREIGN KEY [ ( column [ ,...n ] ) ] REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] ah yes ... so how would one use this and what good would it do?- Onamuji |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-02-22 : 14:01:40
|
| Yeah, they didn't make it very clear, but they meant to say that while you could have a composite foreign key, the number of referencing/referenced columns should match (if you try to set up a foreign key the way you indicated in your original post, you'll get an error 8139, telling you all about it). So like I said, you can't do it.Why would you use composite foreign keys? Well, because oftentimes you need to reference a table whose unique/primary key consists of multiple fields. For instance, let's say you had a table of users: CREATE TABLE users (userid int not null, companyid varchar(100) not null, username varchar(100), blah..., CONSTRAINT pk_users PRIMARY KEY(userid, companyid))Here each user is uniquely identified by a combination of userid and companyid... Therefore, even if Company ABC has a user with a userid=55, and Company XYZ has a user with a userid=55, those are two different users!So if you wanted to keep track of user's documents, you could create a documents table: CREATE TABLE docs (userid int not null, companyid varchar(100) not null, docid int not null, blah..., CONSTRAINT pk_docs (userid, companyid, docid), CONSTRAINT fk_docs_users FOREIGN KEY (userid, companyid) REFERENCES users(userid, companyid))So fk_docs_users constraint is defined here as a composite foreign key simply because referencing by a userid alone is not sufficient to tie a document to a specific user.Edited by - izaltsman on 02/22/2002 14:02:41 |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-22 : 14:39:55
|
| so you would have to have those two columns in your table again ... wouldn't it make sense to just make a primary key on the users table and reference that instead ... are composite foreign keys useless in this way?- Onamuji |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-22 : 20:15:36
|
quote:
wouldn't it make sense to just make a primary key on the users table and reference that instead ... are composite foreign keys useless in this way?
Are you talking about making a surrogate key, like an IDENTITY field on the users table? Well, that brings up a HUGE debate topic that never really gets settled. Some people love meaningless keys and some people hate them. Let's not rehash that again. If you want to read up on the topic, search this site for IDENTITY and you'll find some interesting discussions. In Ilya's example he had a nice meaningful compound key, so it works well, and the composite foreign key is a great benefit.------------------------GENERAL-ly speaking...Edited by - AjarnMark on 02/22/2002 20:16:30 |
|
|
|
|
|
|
|
|
Multiple Columns for a FOREIGN KEY