How to Make a Constraint Deferrable in Oracle

Written by andy carr
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Make a Constraint Deferrable in Oracle
Deferring Oracle constraints is similar to waiting for authorisation to do something. (Jupiterimages/Comstock/Getty Images)

Constraints are designed to prevent accidents in data handling and to preserve integrity of data. In some cases, however, it may be necessary to defer constraints so they do not actually enforce their rules until a specific condition is met. For example, it is possible to defer the rules of a constraint to activate only when a "commit" has been requested.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

  1. 1

    Create two tables in SQLPlus to demonstrate deferrable constraints as follows:

    CREATE TABLE test (

    test_id INTEGER PRIMARY KEY,

    foreign_id INTEGER NOT NULL);

    CREATE TABLE test 2 (

    test2_id INTEGER PRIMARY KEY,

    foreign2_id INTEGER NOT NULL

    );

  2. 2

    Alter the tables in SQLPlus to add foreign key constraints to each other as follows:

    ALTER TABLE test ADD CONSTRAINT test2REF

    FOREIGN KEY (foreign_ID) REFERENCES test 2 (test2_ID)

    INITIALLY DEFERRED DEFERRABLE;

    ALTER TABLE test 2 ADD CONSTRAINT testREF

    FOREIGN KEY (foreign2_ID) REFERENCES test (test_ID)

    INITIALLY DEFERRED DEFERRABLE;

  3. 3

    Insert records into both tables in SQLPlus. This shows that with a deferrable constraint, rows can be added despite the foreign key constraints:

    INSERT INTO test VALUES(1, 2);

    INSERT INTO test 2 VALUES(2, 1);

  4. 4

    Commit the information at the SQLPlus command with:

    Commit;

    This will complete the operation and rows will be added in to both tables.

  5. 5

    Demonstrate how it would fail by running all the commands again in a single operation, but without the deferred constraint. At the SQLPlus command enter:

    -- First drop the tables.

    Drop table test CASCADE constraints;

    Drop table test 2 CASCADE constraints;

    -- Now enter all the previous commands but without the deferrable constraints as follows:

    CREATE TABLE test (

    test_id INTEGER PRIMARY KEY,

    foreign_id INTEGER NOT NULL

    );

    CREATE TABLE test 2 (

    test2_id INTEGER PRIMARY KEY,

    foreign2_id INTEGER NOT NULL

    );

    ALTER TABLE test ADD CONSTRAINT test2REF

    FOREIGN KEY (foreign_id) REFERENCES test 2 (test2_id);

    ALTER TABLE test 2 ADD CONSTRAINT testREF

    FOREIGN KEY (foreign2_id) REFERENCES test (test_id);

    INSERT INTO test VALUES(1, 2);

    INSERT INTO test 2 VALUES(2, 1);

    This version of the script will fail as the constraints have not been deferred awaiting a "COMMIT" command.

Tips and warnings

  • Commands with "--" before them are comments in the code.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.