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

multi column foreign key and null values

P: n/a
Hi all,

I've posted this problem some weeks ago, but noone had a solution.
Perhaps now someone could help me.

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)
)

INSERT INTO T1 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
INSERT INTO T2 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
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 of
the foreign key 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

Feb 1 '06 #1
Share this Question
Share on Google+
7 Replies


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

I've posted this problem some weeks ago, but noone had a solution.
Perhaps now someone could help me.

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)
)

INSERT INTO T1 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
INSERT INTO T2 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
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 of
the foreign key 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


Andy,

this is a logical error because CONSTRAINT t2ID in test table is
combination of two field (MainID, t2ID). You have not this combination
in T2 table (1, null) and this can't work.
I wondering that work in other db.
Maybe other table have disabled this multiple column CONSTRAINT.

Feb 1 '06 #2

P: n/a
Access, like DB2, allows a relationship to be defined with RI not
enforced. But in Access you have to use the GUI to define the
relationship (or DAO, or maybe ADO) because Access SQL does not support
definition of constraints without RI enforced.

So right click your relation and turn off RI and you should be 'fine'.
Note that turning off RI usually implies your code will take
responsibility for RI, not that you've decided to ignore it altogether...

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

I've posted this problem some weeks ago, but noone had a solution.
Perhaps now someone could help me.

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)
)

INSERT INTO T1 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
INSERT INTO T2 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
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 of
the foreign key 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

Feb 1 '06 #3

P: n/a
<am****@yahoo.de> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hi all,

I've posted this problem some weeks ago, but noone had a solution.
Perhaps now someone could help me.

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)
)

INSERT INTO T1 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
INSERT INTO T2 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
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 of
the foreign key 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

Although your post is very clear, have you not over-complicated the problem
by having T1 and T2? Surely what you are saying could be shown with just T1
and the test table.
So ignoring the T2 bit for a minute, the purpose of the relationship is to
only to allow "certain pairs" into the test table fields t1ID and t2ID. So
you can have an entry 8,1,1 because the 1,1 pair is in T1, but you cannot
have values 8,1,2 because the 1,2 pair is not in T1.
Now if you wanted a 1,null pair it would have to exist as a pair in T1 - but
you won't be able to do that because it forms part of the primary key. So
what happens if you assign an autonumber key as the primary key and have a
unique index on the two fields - CREATE UNIQUE INDEX MyIndex ON T1
(ID1,ID2). Well now you can set up your relationships and provided there is
a 1,null pair in T1 there can be a 1,null pair in tblTest.
Hang on, you say! That isn't quite what I wanted - I have to make sure that
the 1,null pair is in T1 before I can use it in the test table - whereas
before (apparantly in DB2) I didn't. But surely this is the very point of
the relationship. Although you may be able to do it in DB2, what is the
point? What real-world data structure do you have that is modelled by this?

I don't know if you have any opportunity to restructure the data, but I
would consider that possibility. Failing that, is there any way you could
do something quick and simple like making the field a required field, but
using another number to 'represent null'. For example, if zero has no
meaning in the data use that, or perhaps a negative number.



Feb 1 '06 #4

P: n/a
Hi Anthony,

the ID1 of table T1 and T2 is a MainID for several tables.

Instead of defining the test-Table like this:

CREATE TABLE test
( Main1ID int Not Null
, t1ID int Not Null

, Main2ID
, t2ID int

, CONSTRAINT t1ID FOREIGN KEY (Main1ID, t1ID) REFERENCES t1(ID1, ID2)
, CONSTRAINT t2ID FOREIGN KEY (Main2ID, t2ID) REFERENCES t2(ID1, ID2)
)

the designer of the database has decided to add the MainID only once,
so I run into the "foreign key" problem in MsAccess.

Andy

Feb 1 '06 #5

P: n/a
Hi Mike,

the RI should be enforced and if possible not in the source.
The only one problem is the multi-column foreign key.

I know that I can turn of the RI, but that's not what I want.
I'm looking for a solution with one column = null.

The question is: under which circumstances can Access handle such a
foreign key?

Andy

Feb 1 '06 #6

P: n/a
<am****@yahoo.de> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
Hi Anthony,

the ID1 of table T1 and T2 is a MainID for several tables.

Instead of defining the test-Table like this:

CREATE TABLE test
( Main1ID int Not Null
, t1ID int Not Null

, Main2ID
, t2ID int

, CONSTRAINT t1ID FOREIGN KEY (Main1ID, t1ID) REFERENCES t1(ID1, ID2)
, CONSTRAINT t2ID FOREIGN KEY (Main2ID, t2ID) REFERENCES t2(ID1, ID2)
)

the designer of the database has decided to add the MainID only once,
so I run into the "foreign key" problem in MsAccess.

Andy

I can't see how you can do replicate this in Access exactly. You didn't
mention whether you would contemplate any restructuring, nor whether the
technique of using say, -1 to represent null would work for you.
Feb 1 '06 #7

P: n/a
Hi Anthony,

yes I have contemplated about restructuring, and the only one solution
would be your -1, but that means all the logic in the source has to be
restructured (if I keep the RI), too - because I need a "-1" record
even though I don't need it really.
I guess I have a long way to go

Thanks
Andy

Feb 2 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.