Multiple referential integrity

[Guest]

I have an Access 2003-database for postcards, in which are (amongst others) a
table for the postcards and another table for the color of the inscriptions
on the postcards. There are inscriptions on the frontside and the backside of
the postcards, so I would like to use the table 'Color of the inscriptions'
for both sides of the postcards.
So I create a relation between the table 'Color of the inscriptions' (the
primary table and the 'one'-side of a 'one-to-many' relation) and TWO fields
of the table 'Postcards' (the fields 'Frontside' and 'Backside'). This is no
problem, as long as I don't try to force referential integrity. When I try to
force referential integrity, I get the message 'Can't make a relation for
which referential integrity has to be forced'. The explanation the program
gives, is (in translation from Dutch): 'Take care that the fields have a
correct primary key or a correct index'.
Yet there are correct primary keys. The field 'Color of the inscriptions' in
the table 'Color of the inscriptions' is a primary key, and there is also a
primary key in the table 'Postcards'. Furthermore, the fields 'Color of the
inscriptions', 'Frontside' and 'Backside' match eachother totally (textfields
of the same length).
According to my book, forcing referential integrity should be possible in
the way I described it.
How can I force referential integrity between 'Color of the inscriptions' on
the one hand and 'Frontside' and 'Backside' simultaneously on the other side?

 

[peregenem]

I create a relation between the table 'Color of the inscriptions' (the
primary table and the 'one'-side of a 'one-to-many' relation) and TWO fields
of the table 'Postcards' (the fields 'Frontside' and 'Backside').

Works for me

CREATE TABLE [Color of the inscriptions]
(Color CHAR(15) NOT NULL PRIMARY KEY);

CREATE TABLE Postcards
(key_col INTEGER NOT NULL PRIMARY KEY,
Frontside CHAR(15)
REFERENCES [Color of the inscriptions] (Color)
ON DELETE SET NULL
ON UPDATE CASCADE,
Backside CHAR(15)
REFERENCES [Color of the inscriptions] (Color)
ON DELETE SET NULL
ON UPDATE CASCADE);

 

[Wolfgang Kais]

Hello John.

:
[snip]

So I create a relation between the table 'Color of the inscriptions'
(the primary table and the 'one'-side of a 'one-to-many' relation)
and TWO fields of the table 'Postcards' (the fields 'Frontside' and
'Backside'). This is no problem, as long as I don't try to force
referential integrity. When I try to force referential integrity, I get
the message 'Can't make a relation for which referential integrity
has to be forced'.

[snip]

Of course, you can. You must create two relationships separately.
When asked if you want to edit the existing relationship, select no.
The second one will be displayed with "Color of the inscriptions_1".

 

[Guest]

Hello Wolfgang,
Your suggestion works perfectly.
Many thanks,
John

Hello John.

:
[snip]

So I create a relation between the table 'Color of the inscriptions'
(the primary table and the 'one'-side of a 'one-to-many' relation)
and TWO fields of the table 'Postcards' (the fields 'Frontside' and
'Backside'). This is no problem, as long as I don't try to force
referential integrity. When I try to force referential integrity, I get
the message 'Can't make a relation for which referential integrity
has to be forced'.

[snip]

Of course, you can. You must create two relationships separately.
When asked if you want to edit the existing relationship, select no.
The second one will be displayed with "Color of the inscriptions_1".