| Constraint problem [message #372608] |
Tue, 27 February 2001 11:09  |
Joe Weinberg
Messages: 1
Registered: February 2001
|
Junior Member |
|
|
I need to create a constraint.
I have several tables created using:
CREATE TABLE table1
( column1 CHAR(3),
column2 CHAR(3),
column3 DATE NOT NULL
column4 VARCHAR(100) NOT NULL,
PRIMARY KEY (column1, column2),
FOREIGN KEY (column1) REFERENCES table2,
FOREIGN KEY (column2) REFERENCES table3)
CREATE TABLE table2
( column1 CHAR(3),
column2 CHAR(10),
column3 CHAR(3)
PRIMARY KEY (column1)
FOREIGN KEY (column2) REFERENCES table4)
CREATE TABLE table3
( column1 CHAR(3),
column2 CHAR(3),
PRIMARY KEY (column1),
FOREIGN KEY (column2) REFERENCES table2)
CREATE TABLE table4
( column1 CHAR(3),
column2 CHAR(3),
PRIMARY KEY (column1))
The names of the tables and columns have been changed to protect the innocent! Note that
table 1 references table 2 which then references table 4. In addition table 1 references
table 3 which references table 2 which then references table 4 again.
The constraint is that if I add a row to table 1, then value of column1 in table 4 should be
the same through each route of references via the FOREIGN KEYS.
Does anyone know how to write the CHECK command for this constraint?
Many thanks
Joe Weinberg
|
|
|
|
| Re: Constraint problem [message #372609 is a reply to message #372608] |
Tue, 27 February 2001 11:47  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Joe
You can easily do this with triggers, using your current design. You'll need the trigger to fire for Each row for both Inserts and Updates.
One thing you can investigate is to do this using referential integrity, where you change the PK on T3 to be an "identifying relationship" i.e. T3's PK is a composite key containing (t2.col1, t3.col2). The trick now is to carry that PK across to T1 as a foreign key. Now you should find that you have t2.col1 carried across to t1 twice. To enforce your rule, try eliminating one of them. Now this t2.col1 carried across to T1 is used in the FK of both T2 and T3. For this to work, you would always need both FK's on T1 to be populated.
Looking at this model though, it appears that T1 is simply a resolves a many to many between T2 and T3, keeping a history of changed recs in T3.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
