2 Replies Latest reply on Mar 14, 2016 3:19 AM by 3181351

    Can we CHECK on object refered by FOREIGN KEY ?

    3181351

      Hi,

       

      I'm very new to Oracle SQL. I've had a very quick class on table creation and an even quicker one on constraints. I don't know if it's possible to do what I want to do. If it is, I don't know how.

      I have a table 'Person' which has an ID, a phone number (that can be NULL) and  other attributes. I have another table of 'Book' that have an authorID which refers to an Person's ID by a foreign key. I want to put a constraint which checks if the Person reffered to by authorID has a phone number (not NULL). There's nothing in Person that tells if it's an author, and authors must have a phone number. Some other types of Peson might have their phone number set to NULL.

       

      Thanks for your time!

       

      Pascal

        • 1. Re: Can we CHECK on object refered by FOREIGN KEY ?
          Barbara Boehmer

          You could enforce this using a trigger, as demonstrated below.

           

           

          SCOTT@orcl> -- person table with primary key:

          SCOTT@orcl> CREATE TABLE person

            2    (id      NUMBER PRIMARY KEY,

            3      phone      NUMBER,

            4      name      VARCHAR2(15))

            5  /

           

          Table created.

           

          SCOTT@orcl> -- book table with foreign key:

          SCOTT@orcl> CREATE TABLE book

            2    (authorid  NUMBER REFERENCES person(id),

            3      title      VARCHAR2(20))

            4  /

           

          Table created.

           

          SCOTT@orcl> -- trigger to check for non-null phone of author:

          SCOTT@orcl> CREATE OR REPLACE TRIGGER check_author_phone

            2    BEFORE INSERT OR UPDATE ON book

            3    FOR EACH ROW

            4  DECLARE

            5    v_phone    person.phone%TYPE;

            6  BEGIN

            7    SELECT phone

            8    INTO   v_phone

            9    FROM   person

          10    WHERE  id = :NEW.authorid;

          11    IF v_phone IS NULL THEN

          12       RAISE_APPLICATION_ERROR (-20001, 'Authors must have phones.');

          13    END IF;

          14  END check_author_phone;

          15  /

           

          Trigger created.

           

          SCOTT@orcl> SHOW ERRORS

          No errors.

          SCOTT@orcl> -- insert into book that fails due to null phone in person:

          SCOTT@orcl> INSERT INTO person (id, phone, name) VALUES (1, null, 'Dr. Seuss')

            2  /

           

          1 row created.

           

          SCOTT@orcl> COMMIT

            2  /

           

          Commit complete.

           

          SCOTT@orcl> INSERT INTO book (authorid, title) VALUES (1, 'The Cat in the Hat')

            2  /

          INSERT INTO book (authorid, title) VALUES (1, 'The Cat in the Hat')

                      *

          ERROR at line 1:

          ORA-20001: Authors must have phones.

          ORA-06512: at "SCOTT.CHECK_AUTHOR_PHONE", line 9

          ORA-04088: error during execution of trigger 'SCOTT.CHECK_AUTHOR_PHONE'

           

           

          SCOTT@orcl> COMMIT

            2  /

           

          Commit complete.

           

          SCOTT@orcl> SELECT * FROM person

            2  /

           

                  ID      PHONE NAME

          ---------- ---------- ---------------

                   1            Dr. Seuss

           

          1 row selected.

           

          SCOTT@orcl> SELECT * FROM book

            2  /

           

          no rows selected

           

          SCOTT@orcl> -- insert into book that is successful with non-null phone in person:

          SCOTT@orcl> UPDATE person SET phone = 1234567 WHERE id = 1

            2  /

           

          1 row updated.

           

          SCOTT@orcl> COMMIT

            2  /

           

          Commit complete.

           

          SCOTT@orcl> INSERT INTO book (authorid, title) VALUES (1, 'The Cat in the Hat')

            2  /

           

          1 row created.

           

          SCOTT@orcl> COMMIT

            2  /

           

          Commit complete.

           

          SCOTT@orcl> SELECT * FROM person

            2  /

           

                  ID      PHONE NAME

          ---------- ---------- ---------------

                   1    1234567 Dr. Seuss

           

          1 row selected.

           

          SCOTT@orcl> SELECT * FROM book

            2  /

           

            AUTHORID TITLE

          ---------- --------------------

                   1 The Cat in the Hat

           

          1 row selected.

          1 person found this helpful
          • 2. Re: Can we CHECK on object refered by FOREIGN KEY ?
            3181351

            Thanks for your answer!
            We learned about the triggers during the following class. My teacher knew it was impossible to do it otherwise, so while we were looking for a way to do it with what we had learned at this time,  he was just laughing at us... Anyways your answer solves the problem! A++++