Re: modify constraint?

From: Van Messner <vmessner_at_bestweb.net>
Date: Sun, 15 Dec 2002 15:07:27 -0500
Message-ID: <uvpo9d7kpli0b0@corp.supernews.com>

The modify does work, but Oracle says it will only work if the constraint was created with the deferrable clause in the first place. Sometimes when you're creating a constraint you just don't know that someday you'll be wanting to modify it to deferrable. In that case you're out of luck.
But if you think you might want to use modify at some point in the future, create the constraint with the deferrable clause when you first make it.

Below is additional.

Immediate / Deferred

    Oracle now offers two choices for constraints immediate and deferred. Immediate constraints are enforced as soon as the statement is executed. If you insert data that violates the constraint you get an error and the statement is rolled back. Deferred constraints are enforced when you commit your transaction but not before. At commit if you have inserted data that violates the constraint you get an error and the entire transaction is rolled back. Deferred constraints let you have one transaction where you enter both parent table and child table data at the same time (when a foreign key exists).

    To use the deferrable choice you must create the constraint with the deferrable clause. Otherwise the constraint will be non-deferrable unless you drop and recreate it. That is, you cannot modify a constraint from not deferrable to deferrable.

    If you have deferrable constraints you can turn deferring on for a session with

                alter session set constraints = deferred;
    For a transaction you can issue a:
                set constraint VAN_C1 deferred;

    If you have deferrable constraints you can also decide whether they should have a default behavior of initially deferred or initially immediate with a statement similar to this:

                alter table VAN add constraint VAN_C1 deferrable initially

immediate;

    And here's one more twist. If your constraints are deferrable, Oracle will use a non-unique index to enforce primary key and unique constraints. This may well be an undesirable consequence of deferrable constraints.

"Owen Gibbins" <oweng_at_autoplan.ws> wrote in message news:errK9.226070$C8.628060_at_nnrp1.uunet.ca...
> What's the point of MODIFY CONSTRAINT if it doesn't work (e.g. to make a
> constraint deferrable).
> Does anyone know why it is necessary to drop and recreate a constraint in
> order to change it?
>
>
Received on Sun Dec 15 2002 - 14:07:27 CST