Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016
    Posts
    1

    Unanswered: Establishing referential constraint with parent table having version column

    Hi All,

    I am trying to establish primary key - foreign key relationship between two tables.

    My parent table have fields as

    Parent_ID
    Version ID

    The first record of any given parent_ID will have version_ID as 1 but any further updates on this record will create a new version with the same Parent_ID and Version_ID as 2. Hence, my Parent_ID and Version_Id field will act as composite primary key of the parent table.

    Now, any child table which references the above parent table has only Parent_ID column as foreign key.

    Oracle doesn't allow this foreign key constraint to be built on child table as foreign key(Parent_Id) doesn't refer to the composite primary key of the parent table.

    To resolve this issue, i can think of a solution as -

    1. Have Version_ID also as a foreign key field in the child table. However, this will create a cumbersome process of updating Version_ID in the child table whenever there is new version created for any given parent ID. So, i want't to avoid this approach.


    Kindly suggest me how i can built the referential constraint based on the above table design or any changes we can do in the table design to have version concept going.


    Thanks,
    Mrinal

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A database trigger might help. Here's an example:
    Code:
    CREATE TABLE t_parent
    (
       parent_id    NUMBER,
       version_id   NUMBER,
       CONSTRAINT pk_par PRIMARY KEY (parent_id, version_id)
    );
    
    CREATE TABLE t_child (parent_id NUMBER);
    
    CREATE OR REPLACE TRIGGER trg_biu_chi
       BEFORE INSERT OR UPDATE
       ON t_child
       FOR EACH ROW
    DECLARE
       l_dummy   NUMBER;
    BEGIN
       SELECT MAX (p.parent_id)
         INTO l_dummy
         FROM t_parent p
        WHERE p.parent_id = :new.parent_id;
    
       IF l_dummy IS NULL
       THEN
          raise_application_error (-20001, 'That PARENT_ID does not exist');
       END IF;
    END;
    Test it by inserting/updating different values into both T_PARENT and T_CHILD tables.

    Just a note: I used the MAX function to avoid TOO-MANY-ROWS exception; without it, I'd have to handle it properly. It also means that IF wouldn't be needed - NO-DATA-FOUND should in that case also be handled in the exception handling section. Something like this (which is, by the way, probably a better way to handle the situation):
    Code:
    CREATE OR REPLACE TRIGGER trg_biu_chi
       BEFORE INSERT OR UPDATE
       ON t_child
       FOR EACH ROW
    DECLARE
       l_dummy   NUMBER;
    BEGIN
       SELECT p.parent_id
         INTO l_dummy
         FROM t_parent p
        WHERE p.parent_id = :new.parent_id;
    EXCEPTION
       WHEN TOO_MANY_ROWS
       THEN
          NULL;
       WHEN NO_DATA_FOUND
       THEN
          raise_application_error (-20001, 'That PARENT_ID does not exist');
    END;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •