CODEWITHSUNDEEP
sqldatabaseoracle-database
devPash
devPash

Reputation: 57

Oracle SQL, conditional foreign key

I have a problem with implementing DB. I have 2 tables:

 CREATE TABLE TAB1
 (
    TAB1_ID INT NOT NULL PRIMARY KEY,
    TAB1_BOOL VARCHAR(4) NOT NULL CHECK (TAB1_BOOL IN('OPT1', 'OPT2'))
 );
 CREATE TABLE TAB2
 (
   TAB2_ID INT NOT NULL
 );

I would like to make TAB2_ID as foreign key of TAB1_ID, but TAB2_ID can be only these TAB1_IDs which rows in table TAB1 are marked with TAB1_BOOL == OPT1. For example:

TAB1 contains 2 rows: 
1. 'TAB1_ID==1' 'TAB1_BOOL==OPT1', 
2. 'TAB1_ID==2' 'TAB1_BOOL==OPT2'.
Now, statement like:
INSERT INTO TAB2 (TAB2_ID) VALUES (2);
should be rejected because row with TAB1_ID==2 is marked with OPT2

Is it possible to make this in Oracle SQL ?

Upvotes: 1

Views: 1785

Answers (1)

Trinimon
Trinimon

Reputation: 13967

This can be achieved by using a trigger:

 CREATE TABLE TAB1 (
    TAB1_ID INT NOT NULL PRIMARY KEY,
    TAB1_BOOL VARCHAR(4) NOT NULL CHECK (TAB1_BOOL IN('OPT1', 'OPT2'))
 );

 CREATE TABLE TAB2 (
   TAB2_ID INT NOT NULL,
   FOREIGN KEY (TAB2_ID) REFERENCES TAB1 (TAB1_ID)
 );

CREATE OR REPLACE TRIGGER CHECK_TAB2_ID
    BEFORE UPDATE OR INSERT ON TAB2 FOR EACH ROW
DECLARE 
    CURSOR CHECK_CURSOR IS
        SELECT TAB1_BOOL
          FROM TAB1 
         WHERE TAB1_ID = :new.TAB2_id;
    V_TAB1_BOOL VARCHAR(4);
BEGIN
    OPEN CHECK_CURSOR;
    FETCH CHECK_CURSOR INTO V_TAB1_BOOL;
    IF (V_TAB1_BOOL <> 'OPT1') THEN
       CLOSE CHECK_CURSOR;
       raise_application_error(10000, 'Wrong value in TAB1');
    END IF;
    CLOSE CHECK_CURSOR;
END;

INSERT INTO TAB1 VALUES (1, 'OPT1');
INSERT INTO TAB1 VALUES (2, 'OPT2');

INSERT INTO TAB2 (TAB2_ID) VALUES (1);

There is still a small error in the trigger above. I have currently no access to an Oracle database, so I can hardy fix it. Here's my Fiddle.

p.s.: ok, just a ; missing in the Fiddle - here's an update.

Upvotes: 1

Related Questions