Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jaspreet.

Asked: January 23, 2018 - 11:50 am UTC

Last updated: April 28, 2023 - 10:25 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Team,

I was just playing around with some stuff so thought sharing with you.

My understanding for Primary key was that Oracle uses Unique Index to enforce primary key constraint on table's column to be unique.

Below is the code that I tried, in which I created Non-Unique Index on Primary Key column and tried to insert duplicate data but it still give error.

So my question is how Oracle is enforcing Primary Key constraint.

SQL> CREATE TABLE TAB_TEST ( ID NUMBER, NAME VARCHAR2(10) );

Table created.

SQL> ALTER TABLE TAB_TEST ADD CONSTRAINT ID_PK PRIMARY KEY (ID) USING INDEX ( CREATE INDEX ID_0P ON TAB_TEST(ID ASC) );

Table altered.

SQL> COL INDEX_NAME FOR A10
SQL> COL INDEX_TYPE FOR A10
SQL> COL TABLE_NAME FOR A10
SQL> COL UNIQUENESS FOR A15
SQL> COL STATUS FOR A10
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS, STATUS FROM DBA_INDEXES WHERE TABLE_NAME = 'TAB_TEST';

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS      STATUS
---------- ---------- ---------- --------------- ----------
ID_0P      NORMAL     TAB_TEST   NONUNIQUE       VALID

SQL> INSERT INTO TAB_TEST VALUES ( 1, 'J1' );

1 row created.

SQL> INSERT INTO TAB_TEST VALUES ( 2, 'J2' );

1 row created.

SQL> INSERT INTO TAB_TEST VALUES ( 3, 'J3' );

1 row created.

SQL> INSERT INTO TAB_TEST VALUES ( 1, 'J1' );
INSERT INTO TAB_TEST VALUES ( 1, 'J1' )
*
ERROR at line 1:
ORA-00001: unique constraint (FC_DBA1.ID_PK) violated

and Connor said...

We can use a non-unique index to enforce a unique constraint.

This can be useful for a deferrable constraint, ie, you are allowed to violate it temporarily but the data must be valid at the time of commit.

Here is an article on deferred constraints

https://asktom.oracle.com/Misc/oramag/on-deferring-and-bulking-up.html


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Primary Key with Duplicate Values

Aminu Ismaila, March 30, 2020 - 9:38 pm UTC

Hi Team,

I have come across a puzzling case illustrated by Alex Fatkulin at Pythian whereby a Primary Key can have duplicates under certain conditions when using a deferred PK constraint. This is the URL: https://blog.pythian.com/deferrable-constraints-in-oracle-11gr2-may-lead-to-logically-corrupted-data/

This problem he described is still reproducible in 12c (12.1.0.2.0). Please find below my reproduction:

In First Session:

SQL> create table def_bug(n number primary key deferrable initially deferred);

Table created.

SQL> insert into def_bug values (1);

1 row created.

SQL> insert into def_bug values (2);

1 row created.

SQL> commit;

Commit complete.

-- Checking to make sure constraint is working
SQL> insert into def_bug values (1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (AISMAILA.SYS_C0010310) violated


In Second Session:

SQL> update def_bug set n=3 where n=2;

1 row updated.

Back in First Session (This will hang)

SQL> update def_bug set n=3 where n<=2;

1 row updated.


Now Commit Second session:


SQL> commit;

Commit complete.


And Commit First Session:

SQL> commit;

Commit complete.


Now See Contents of table:

SQL> select * from def_bug;

         N
----------
         3
         3


Your comments on this will be much appreciated.

Thanks





Connor McDonald
March 31, 2020 - 1:17 am UTC

Reproducible in 19 as well.

I'll log a bug (or find if there is already an existing bug logged)

non unique index used for primary key constraint for performance reasons

lh, March 31, 2020 - 8:35 am UTC

Hi

One common (?) performance method is define non unique index based on columns (A,B) and define primary key constraint on colum n A.

Now select statements of type:
select B from table where A = ? will only access index.

This is useful e.g. when B is status column.


lh
Chris Saxon
March 31, 2020 - 8:40 am UTC

Good point.

Radu Parvu, April 23, 2023 - 6:55 pm UTC

http://www.oracle.com/technetwork/testcontent/o31asktom-354139.html Is broken!

I have a similar question: we have a huge five column unique index created automatically when we added the 5 column unique constraint, can we use a four column non-unique index to support the constraint? I suppose not but I still cannot find anywhere in the documentation the conditions an index should fulfill in order to support an unique constraint.
Chris Saxon
April 28, 2023 - 10:25 am UTC

Thanks, link fixed.

The index for a primary/unique constraint must contain all the columns in the constraint.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.