I have two tables with foreign keys to the same parent table and a junction table between them to map a many to many relationship. I want to know how best to enforce data integrity in the junction table so that both parents' parent is the same.
CREATE TABLE Parent ( ParentCode char(3) NOT NULL, Name varchar(50) NOT NULL, PRIMARY KEY (ParentCode) ); CREATE TABLE Child1 ( Child1Code char(3) NOT NULL, Name varchar(50) NOT NULL, ParentCode char(3) REFERENCES Parent(ParentCode) PRIMARY KEY (Child1Code) ); CREATE TABLE Child2 ( Child2Code char(3) NOT NULL, Name varchar(50) NOT NULL, ParentCode char(3) REFERENCES Parent(ParentCode) PRIMARY KEY (Child1Code) ); CREATE TABLE ChildJunction ( Child1Code char(3) NOT NULL, Child2Code char(3) NOT NULL, Attribute int NOT NULL, PRIMARY KEY (Child1Code, Child2Code) );
How do I ensure that the Child1 and Child2 in ChildJunction have the same Parent?
The best I have so far is that ChildJunction includes a ParentCode column, I create a UNIQUE constraint on Child1Code + ParentCode in Child1 and a UNIQUE constraint on Child2Code + ParentCode in Child2 and the FOREIGN KEYS from ChildJunction are ChildJunction.Child1Code = Child1.Child1Code AND ChildJunction.ParentCode = Child1.ParentCode and ChildJunction.Child2Code = Child2.Child2Code AND ChildJunction.ParentCode = Child2.ParenCode.
But that leaves me with redundant data in ChildJunction (i.e. ParentCode).
Am I missing something? This must be a common problem? have I designed the Child tables wrong? Or the Parent table?
Any help gratefully received...