By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,225 Members | 2,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,225 IT Pros & Developers. It's quick & easy.

Foreign Key with multiple columns and null value

P: n/a
Hi all,

I have the following tables:

Create Table T1( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )

Create Table T2( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )

CREATE TABLE test( MainID int Not Null, t1ID int Not Null, t2ID int
, CONSTRAINT t1ID FOREIGN KEY (MainID, t1ID) REFERENCES t1(ID1, ID2)
, CONSTRAINT t2ID FOREIGN KEY (MainID, t2ID) REFERENCES t2(ID1, ID2)
)

If I try to execute the following sql I get an error, because t2ID is
not a valid key for T2:

INSERT INTO test ( MainID, t1ID, t2ID ) VALUES (1, 1, null);

In other databases this is no problem, means I can fill one column with
a value and set the other column of the key to null. I'm porting a Db2
database to access and many tables have multiple column keys with only
the MainID filled.

Does anybody knows a workaround?

Thanks in advance
Andy

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

am****@yahoo.de wrote:
Hi all,

I have the following tables:

Create Table T1( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )

Create Table T2( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )

CREATE TABLE test( MainID int Not Null, t1ID int Not Null, t2ID int
, CONSTRAINT t1ID FOREIGN KEY (MainID, t1ID) REFERENCES t1(ID1, ID2)
, CONSTRAINT t2ID FOREIGN KEY (MainID, t2ID) REFERENCES t2(ID1, ID2)
)

If I try to execute the following sql I get an error, because t2ID is
not a valid key for T2:

INSERT INTO test ( MainID, t1ID, t2ID ) VALUES (1, 1, null);

In other databases this is no problem, means I can fill one column with
a value and set the other column of the key to null. I'm porting a Db2
database to access and many tables have multiple column keys with only
the MainID filled.


I ran this in SQL Server and it failed with the following message:

INSERT statement conflicted with TABLE FOREIGN KEY constraint 't1ID'.
The conflict occurred in database 'EsTest', table 'T1'.

I can't think of a workaround, but I would suspect that your data
design is fatally flawed.

Good luck!

Edward

Nov 13 '05 #2

P: n/a
Hi Edward,

the message you get has nothing to do with a flawed data design, insert
this two statements

INSERT INTO T1 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
INSERT INTO T2 ( ID1, ID2, Description ) VALUES (1, 1, 'test');

and then insert the data into the test-table.

Sorry, I forget this two Insert-Statements.

Andy

Nov 13 '05 #3

P: n/a
Hi all,

does noone have the same problem or has a solution?

Andy

Nov 13 '05 #4

P: n/a
rkc
am****@yahoo.de wrote:
Hi all,

does noone have the same problem or has a solution?


You original post is almost 3 weeks old.
You might want to re-post and perhaps re-word your
question.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.