Home » SQL & PL/SQL » SQL & PL/SQL » constraints
constraints [message #222639] Mon, 05 March 2007 14:23 Go to next message
rcd70
Messages: 14
Registered: April 2006
Location: Auckland, New Zealand
Junior Member
Hi

Can anyone please tell me that if I disable a primary key constraint, is the unique index associated with the primary key disabled too.

Thanks
Re: constraints [message #222649 is a reply to message #222639] Mon, 05 March 2007 15:11 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, yes.
SQL> select * from test where id = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 3102587058

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    25 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    25 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_TEST |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL>

Now let's disable the primary key constraint:
SQL> alter table test disable constraint pk_Test;

SQL> select * from test where id = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Re: constraints [message #222659 is a reply to message #222649] Mon, 05 March 2007 19:02 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you create the index first, then create the constraint, the index is retained when you disable the constraint. If the index is created as a result of the constraint creation, then the index is DROPPED (not disabled) when the constraint is disabled.

You can supply an optional clause to the DISABLE CONSTRAINT to have it retain the index. This is covered in the doco.

Ross Leishman
Previous Topic: SQL functionality by Oracle Version
Next Topic: INSERT INTO with multiple (!) VALUES possible ?
Goto Forum:
  


Current Time: Fri Dec 09 05:34:16 CST 2016

Total time taken to generate the page: 0.11946 seconds