Foreign Key with multiple columns and null value

[am72de]

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

[teddysnips]

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

[am72de]

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

[am72de]

Hi all,

does noone have the same problem or has a solution?

Andy

[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.