Re: Help for Oracle documentation/turning off foreign key constraints

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 24 Dec 2004 17:14:58 -0500
Message-ID: <pan.2004.12.24.22.14.54.160351@sbcglobal.net>

On Fri, 24 Dec 2004 06:37:29 -0800, Thomas Kyte wrote:

> while we can do this (you can enable novalidate the constraint), I see nothing
> but heartburn coming from such a concept.

Allow me to redirect you to a great site, http://asktom.oracle.com There, the guru says:
(http://asktom.oracle.com/pls/ask/f?p=4950:8:1228413951318418547::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:8806498660292)

and we said...

By default a non-deferrable UNIQUE or PRIMARY KEY constraint will attempt to create a unqiue index. That is what is happening here.

You have a choice

create the constraint deferrable. This will create a non-unique constraint. When you disable the constraint, you have a nice side effect that the INDEX stays in place.

ps$tkyte_at_ORA920> create table t (a number, constraint uniq_a UNIQUE(a) DEFERRABLE );
Table created.

ops$tkyte_at_ORA920> select index_name from user_indexes where table_name = 'T';

INDEX_NAME

UNIQ_A ops$tkyte_at_ORA920> alter table t disable constraint uniq_a; Table altered.

ops$tkyte_at_ORA920> select index_name from user_indexes where table_name = 'T';

INDEX_NAME

UNIQ_A ops$tkyte_at_ORA920> insert into t values ( 1 ); 1 row created.

ops$tkyte_at_ORA920> insert into t values ( 1 ); 1 row created.

ops$tkyte_at_ORA920> alter table t enable novalidate constraint uniq_a; Table altered.

Or, you can create the constraint as normal, lose the index (which may be equally as desirable as keeping it depending on your circumstances of course) and create a NON-Unique index manually:

ops$tkyte_at_ORA920> create table t (a number, constraint uniq_a UNIQUE(a)); Table created.

ops$tkyte_at_ORA920> select index_name from user_indexes where table_name = 'T';

INDEX_NAME

UNIQ_A ops$tkyte_at_ORA920> alter table t disable constraint uniq_a; Table altered.

ops$tkyte_at_ORA920> select index_name from user_indexes where table_name = 'T'; no rows selected

ops$tkyte_at_ORA920> insert into t values ( 1 ); 1 row created.

ops$tkyte_at_ORA920> alter table t enable novalidate constraint uniq_a; alter table t enable novalidate constraint uniq_a *
ERROR at line 1:
ORA-02299: cannot validate (OPS$TKYTE.UNIQ_A) - duplicate keys found

ops$tkyte_at_ORA920> create index t_idx on t(a); Index created.

ops$tkyte_at_ORA920> alter table t enable novalidate constraint uniq_a; Table altered.

You two guys have amazingly similar prompts :). The same thing can also be done with foreign key constraints. ENABLE NOVALIDATE can be useful when creating small, demo databases as a subset from the production database.

Merry Christmas Tom, you are doing an amazing job, both on asktom and here! In addition to that, Effective Oracle by design is one of the most useful books on the market.

-- 
Artificial Intelligence is no match for natural stupidity.

Received on Fri Dec 24 2004 - 16:14:58 CST