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 |
|
TheSmithz
Starting Member
1 Post |
 Posted - 2009-05-14 : 10:04:42
|
| Consider this problem:A database has Table ATable B and C reference Table A.The PK of B and C is an identity, and they both have a FK back to Table A.I have a table D, which is a join table, referencing table B and C.Table D has PK of an identity and FK's back to B and C.A ..... two rows ... A1 and A2B ... two rows ... B1 (FK to A1) & B2 (FK to A2)C ... two rows .... C1 (FK to A1) & C2 (FK to A2) D ..... I can add the following to D .... FK to B1 and FK to C1 ..... this is wrong.I want to make sure that whatever B and C are referenced in D both point back to same A !!!With composite keys this is possible .... but I can’t see how it can be done in the database with identities being used everywhere.Alan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-14 : 10:44:27
|
| do you mean you need to set foreign key reference from same to column to two other columns of two different tables? |
 |
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2009-05-18 : 15:42:06
|
| See if this meets your requirements:CREATE TABLE A (a INT NOT NULL PRIMARY KEY);CREATE TABLE B (b INT NOT NULL PRIMARY KEY, a INT NOT NULL REFERENCES A (a), UNIQUE (b,a));CREATE TABLE C (c INT NOT NULL PRIMARY KEY, a INT NOT NULL REFERENCES A (a), UNIQUE (c,a));CREATE TABLE D (d INT NOT NULL PRIMARY KEY, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, FOREIGN KEY (b,a) REFERENCES B(b,a), FOREIGN KEY (c,a) REFERENCES C(c,a), UNIQUE (b,c) );It doesn't matter whether these tables have IDENTITY keys or not, as long as you reference the composite key from table D. This is a SQL "feature" that's often overlooked. UNIQUE constraints can actually be superkeys, not just candidate keys. "FOREIGN KEY" constraints are allowed to reference any superkey that matches the set of columns in a UNIQUE constraint. |
|
|
|
|
|
|
|
|
Identity Problem with Many to Many table