databaseoracle-database

Options to enforce a logical constraint in an Oracle database


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?


Solution

  • 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.