Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: primary key deferrable initially immediate

Re: primary key deferrable initially immediate

From: Sergey M <msu_at_pronto.msk.ru>
Date: Fri, 26 Oct 2001 16:21:37 +0400
Message-ID: <9rbkgp$2cu7$1@serv2.vsi.ru>

"Ted Chyn" <tedchyn_at_yahoo.com> сообщил/сообщила в новостях следующее: news:44a19320.0110241653.a442c7c_at_posting.google.com...
> all,
>
> Is the following listing an expected behavoir for primary key ?
>
> what is 'defferrable initially immediate' to primary constraint and index
?
>
> thnx ted chyn
>
>
> SQL> create table t
> 2 ( x int,
> 3 constraint t_pk primary key (x)
> 4 deferrable initially immediate );
>
> Table created.
>
> SQL> select index_name from user_indexes where table_name='T';
>
> INDEX_NAME
> ------------------------------
> T_PK
>
> SQL> alter table t drop constraint t_pk;
>
> Table altered.
>
> SQL> select index_name from user_indexes where table_name='T';
>
> INDEX_NAME
> ------------------------------
> T_PK ### note index still here
>
> =================
> SQL> create table q (a number, constraint pk_q primary key (a));
>
> Table created.
>
> SQL> select index_name from user_indexes where table_name='Q';
>
> INDEX_NAME
> ------------------------------
> PK_Q
>
> SQL> alter table q drop constraint pk_q;
>
> Table altered.
>
> SQL> select index_name from user_indexes where table_name='Q';
>
> no rows selected #### note index is gone when primary key
constraints
> is dropped.

Deferrable unique constraints always use nonunique indexes. When you remove a deferrable constraint, its index remains. This is convenient because the storage information remains available after you disable a constraint. Not-deferrable unique constraints and primary keys also use a nonunique index if the nonunique index is placed on the key columns before the constraint is enforced.

Sergey M. Received on Fri Oct 26 2001 - 07:21:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US