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 |
|
pacerier
Starting Member
5 Posts |
 Posted - 2009-12-07 : 22:40:43
|
| I have a table (table A), and its primary key is a combination of column A and column B. so if i wanted to reference table A with table B's foreign key, does it mean that table B must have 2 columns each referencing each of table A's primary key?however there may be problems in which table B's refcol A matches an allowed value in table A's col A and table B's refcol B matches an allowed value in table A's col B However the combination colA+colB VS combination refcolA + refcolB does not exist. is there any better way to do this?--- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-12-07 : 22:53:45
|
| Chop Suey?WhoaCare to post examples?There should be no problem having composite keysSOME people however, get around this by creating a FALSE, ARTIFICIAL "KEY" that represents the actual Natural KeyThese are called Surrogates (IDENTITY or datetime Columns)I Strongly suggest you learn to model data CORRECTLY and create these Natural keysBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-12-07 : 22:56:23
|
quote:
Originally posted by tkizer
The foreign key does not have to match exactly the primary key.
Ummmmmmmmmm....I could go with that in a non-identifying relationship...but you still would need to ref the whole key...IF it exists...Otherwise, there is no real relationshipBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
pacerier
Starting Member
5 Posts |
Posted - 2009-12-08 : 00:51:33
|
| i mean this:TABLE1PK1--PK2-1----2-2----7-8----6-1----9-6----2Primary key for table1 of course is a combination of attribute PK1 and PK2now i want to reference Table 1 this is what i'm currently doingTABLE2FK1_referencing_PK1-----FK2_referencing_PK2-------1-------------------------2-------2-------------------------7-------8-------------------------6-------1-------------------------9-------6-------------------------2However when this is not good because I am able to insert a value such as:-------6-------------------------9------where 6 exist in PK1 and 9 exists in PK2 However 6 and 9 does not exist in combination. is there anyway to fix this such that the database will reject such referenced nonexisting combinations of composite primary keys?--- |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pacerier
Starting Member
5 Posts |
Posted - 2009-12-08 : 02:34:49
|
| sorry but how is that possible, or how does the syntax allows it to be possible?--- |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Composite Foreign Key?