Mar 142014
 

Somehow I ended up with two rows in my table both with the same primary key value:

select the_pk 
from   dodgy_data;

    THE_PK
----------
         1
         1

The primary key does exist and THE_PK is the only column in it, so we can’t insert another row with THE_PK = 1:

select constraint_name, constraint_type
from   user_constraints
where  table_name = 'DODGY_DATA';

CONSTRAINT_NAME      CONSTRAINT_TYPE
-------------------- ---------------
TAB_PK               P              

select column_name 
from   user_cons_columns
where  constraint_name = 'THE_PK';

COLUMN_NAME
-----------
THE_PK     

insert into dodgy_data ( the_pk )
values ( 1 );

SQL Error: ORA-00001: 
  unique constraint (CHRIS.TAB_PK) violated

How? Answers in the comments!

  7 Responses to “SQL Quiz: How did my primary key get duplicate values?”

  1. Hi Chris,

    that’s actually a nice one. Thought about it before, but never tried it.

    CREATE TABLE dodgy_data (the_pk NUMBER);

    CREATE INDEX idx_dodgy_data_the_pk ON dodgy_data (the_pk);

    ALTER TABLE dodgy_data
    ADD CONSTRAINT tab_pk
    PRIMARY KEY (the_pk)
    USING INDEX idx_dodgy_data_the_pk;

    INSERT INTO dodgy_data (the_pk) VALUES (1);

    ALTER TABLE dodgy_data
    DISABLE PRIMARY KEY KEEP INDEX;

    –This works
    INSERT INTO dodgy_data (the_pk) VALUES (1);

    ALTER TABLE dodgy_data
    MODIFY CONSTRAINT tab_pk
    ENABLE NOVALIDATE;

    –This does not!
    INSERT INTO dodgy_data (the_pk) VALUES (1);

    DROP TABLE dodgy_data;

    I really dislike NOVALIDATE. Of course its there for a reason, but you have to remember to check the “validated” column of the user_constraints view.
    Also I’m pretty sure that not everyone is aware that you don’t need an unique index as base-object for a primary key and that a non-unique index will do the trick as well. Or even that the index can cover more columns as long as the primary key a a prefix of the index.

    Best regards,
    Salek

    • Nicely done Salek :)

      I’d go a bit further with your final statement – I think not many people know that creating a primary key actually creates a unique index in the background, nevermind that you can police it with a non-unique index!

  2. Hello Chris,

    it’s not exactly for your scenario, I just though to share it here, allowing till commit time to have the PK violated in the transaction.


    CREATE TABLE dodgy_data (the_pk NUMBER);

    ALTER TABLE dodgy_data
    ADD CONSTRAINT tab_pk
    PRIMARY KEY (the_pk) INITIALLY DEFERRED DEFERRABLE;

    That will create a non-unique index as well and since the constraint is deferrable, you can violate it in the transaction.


    INSERT INTO dodgy_data (the_pk) VALUES (1);
    INSERT INTO dodgy_data (the_pk) VALUES (1);
    INSERT INTO dodgy_data (the_pk) VALUES (1);
    ...

    commit gives error and rollback the transaction

    ORA-02091: transaction rolled back
    ORA-00001: unique constraint (D_H.TAB_PK) violated

    Regard,
    Peter

    • Nice example Peter – I hadn’t realised that creating a deferred primary key would create a non-unique index. Makes sense when you think about it though :)

  3. Hi Chris,

    uo to 11.2.0.4 you can get this result even with a constraint that is validated and enabled – if your insert uses direct path: Randolf Geist recently mentioned the corresponding bug (that has been there for a long time) on OTN (<a href="https://community.oracle.com/thread/3526877&quot;) and some tests showed that the problem still existed in 11.2.0.3 but was fixed in 12.1 (and the fix was backported to 11.2.0.4).

    Regards

    Martin

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)