Suppose I have a pair of tables, A and B, that are in a standard one to many relationship, with B containing a foreign key to A. I’d like to introduce a table C with a many-to-many relationship to B, but with the restriction that, for any given record in C, all of the associated records in B are associated with a single record in A. A simple link table between C and B provides the desired many-to-many relationship, but it does nothing to prevent a single record in C from being associated with B's tied to different A's. The only way I can think of accomplishing this in the database is to create a trigger to check and enforce the condition. Are there other applicable techniques?
Include the primary key for table a in tables b, c and in the bridging table used to define the many-to-many relationship between b and c:
CREATE TABLE a (id NUMBER PRIMARY KEY);
CREATE TABLE b (
id NUMBER PRIMARY KEY,
a_id REFERENCES a (id),
UNIQUE (id, a_id)
);
CREATE TABLE c (
id NUMBER PRIMARY KEY,
a_id REFERENCES a(id),
UNIQUE (id, a_id)
);
CREATE TABLE bc_bridge (
a_id,
b_id,
c_id,
FOREIGN KEY (a_id, b_id) REFERENCES b (a_id, id),
FOREIGN KEY (a_id, c_id) REFERENCES c (a_id, id)
);
Then table c can be related to many bs (and vice-versa) but related rows in those tables are always only related to a single a.