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?
3
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.
0
You can use a constraint on a materialized view to enforce the conditions.
If you have the tables a, b, c with a bridging table representing 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
);
CREATE TABLE bc_bridge (
b_id REFERENCES b (id),
c_id REFERENCES c (id)
);
Then you can create a materialized view that finds the minimum and maximum a values for each c:
CREATE MATERIALIZED VIEW LOG ON bc_bridge
WITH SEQUENCE, ROWID(b_id, c_id)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON b
WITH SEQUENCE, ROWID(id, a_id)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW bc_min_max_a_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT bc.c_id,
MIN(b.a_id) AS a_min,
MAX(b.a_id) AS a_max
FROM bc_bridge bc,
b
WHERE b.id = bc.b_id
GROUP BY bc.c_id;
And use a constraint on the materialized view to enforce that the minimum and maximum a values should be the same.
ALTER TABLE bc_min_max_a_mv ADD CHECK( a_min = a_max );
(Note: Untested, but it should give you the general idea.)
6