Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Unanswered: PK that is not unique -- possible in oracle??

    could someone with access to an oracle test system please test something for me?

    this sql was sent to me by someone who says that oracle allows you to declare a non-unique PK, and i don't have an oracle system to test this on myself --
    Code:
    create 
     table test1 
         ( col1 integer not null
         , col2 integer );
    
    create index test1_col1_ix on test1 (col1);
    
    alter table test1 
    add constraint test1_pk primary key (col1) 
    using index test1_col1_ix;
    once the table is created, you are supposed to be able to verify it like this --
    Code:
    select * from user_indexes where table_name = 'test1'
    
    select * from user_constraints where table_name = 'test1'
    and then you are supposed to be able to enter duplicate PKs like this --
    Code:
    insert into test1  values (1,1);
    
    insert into test1  values (1,1);
    does that actually work?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    On 8.1.7 it fails on the alter table add constraint...

    Code:
    create 
     table test1 
         ( col1 integer not null
         , col2 integer );
    
    create index test1_col1_ix on test1 (col1);
    
    alter table test1 
    add constraint test1_pk primary key (col1) 
    using index test1_col1_ix;
    
    ORA-01735: invalid ALTER TABLE option
    But on 10g it allowed the table and constraint creation, but then failed on the insert....

    Code:
    ORA-00001: unique constraint TEST.TEST1_PK violated
    Hth
    Bill
    Last edited by billm; 03-30-06 at 11:35.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, bill, very helpful

    i thought that a non-unique PK sounded a little weird...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You could however create it "deferrable initially deferred", in which case the uniqueness would not be checked until you commit.

    Or you could create it disabled, add some non-unique values, then enable novalidate:

    Code:
    SQL> CREATE TABLE test1
      2  ( col1 INTEGER NOT NULL
      3  , col2 INTEGER );
    
    Table created.
    
    SQL> CREATE INDEX test1_col1_ix ON test1(col1);
    
    Index created.
    
    SQL> ALTER TABLE test1
      2  ADD CONSTRAINT test1_pk PRIMARY KEY (col1)
      3  DEFERRABLE INITIALLY DEFERRED;
    
    Table altered.
    
    SQL> INSERT INTO test1 VALUES (1,1);
    
    1 row created.
    
    SQL> r
      1* INSERT INTO test1 VALUES (1,1)
    
    1 row created.
    
    SQL> COMMIT;
    COMMIT
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-00001: unique constraint (WILLIAMR.TEST1_PK) violated
    
    
    SQL> ALTER TABLE test1 DROP PRIMARY KEY;
    
    Table altered.
    
    SQL> ALTER TABLE test1
      2  ADD CONSTRAINT test1_pk PRIMARY KEY (col1)
      3  DISABLE;
    
    Table altered.
    
    SQL> INSERT INTO test1 VALUES (1,1);
    
    1 row created.
    
    SQL> r
      1* INSERT INTO test1 VALUES (1,1)
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> ALTER TABLE test1 ENABLE NOVALIDATE CONSTRAINT test1_pk;
    
    Table altered.
    (Oracle 9.2.0.1)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, DEFERRABLE INITIALLY DEFERRED

    and ENABLE NOVALIDATE, too

    terms only a DBA could love

    or even know about, eh



    thanks for the info

    i have referred the person to this thread, and i trust he will find it helpful

    as for myself, i'm going to continue to believe, despite the above, which i'm not sure i understood, that a primary key must be unique
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You already know that, Rudy, but - as I have some time to spend, I hope you won't mind me saying a few words.

    "Someone says that Oracle allows you to declare a non-unique PK" -> maybe he meant to say "a non-unique unique key"? (sounds stupid, though). If a NULL value is inserted into the column, unique key constraint is not violated. Something like this:
    Code:
    SQL> create table test (col1 integer, col2 integer);
    
    Table created.
    
    SQL> create index test_col1_idx on test (col1);
    
    Index created.
    
    SQL> alter table test add constraint test_uk unique (col1)
      2  using index test_col1_idx;
    
    Table altered.
    
    -> entering NULL values we will not violate unique key constraint ...
    
    SQL> insert into test values (null, 1);
    
    1 row created.
    
    SQL> insert into test values (null, 1);
    
    1 row created.
    
    -> ... but entering the same values into the 'col1', constraint will be violated
    
    SQL> insert into test values (1, 2);
    
    1 row created.
    
    SQL> insert into test values (1, 3);
    insert into test values (1, 3)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.TEST_UK) violated
    
    SQL>
    Besides (not that it has anything to do with the subject), original posters' CREATE TABLE statement has 'col1' column declared as NOT NULL which is not necessary as adding a primary key on that columns automatically prevents entering null values into 'col1'.

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I really don't consider deferred constraints or 'enable novalidate' to be fair tests of Oracle's PK management.

    With deferred constraints you are explicitly stating to Oracle "please don't consider constraints until commit point - my inner workings require you to ignore me until I'm finished". This is the whole purpose of deferrable constraints. The fact that Oracle fails on the commit says that Oracle is doing it's thing exactly as expected.

    With "enable novalidate", well that's like someone giving you money and saying "don't worry, you dont need to count it, I've counted it already" just before you hand over your house :-) There has to be a lot of trust involved in that. If you say to Oracle "trust me, I know what I'm doing" then, well, so it should :-)

    I believe the ability to allow deferred constraints and index based constraints to be disabled then re-enabled without validate are good offerings from Oracle (and I sometimes rely heavily on them). But, and this is a big but... if these offerings allow some way to inadvertently circumvent a PK with traditional DBA management statements (such as hinted to by Rudy's colleague and questioned by WilliamR, Littlefoot and myself) I would be concerned, very very concerned!!!

    If such a route exists, I suspect it would be a symantic/syntactical/progression type bug and I would also suspect this is something which Oracle have already tried to correct (as hinted to by the major behaviour difference between 8i and 10g).

    I really hope it's not true :-)

    Kind Regards
    Bill
    Last edited by billm; 03-30-06 at 19:31.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Mar 2006
    Posts
    1

    Why using nonunique indexes to support primay keys

    an additional comment from the one who provides the statements Ruby used in the posting starting the thread:

    1) This "feature" is introduced in oracle9, therefore the test in oracle8.1.7 returns an error.

    2) Primary key constraint = uniqueness constraint + not null constraint
    Constraint are logical constructs. Indexes are a implementation constructs. It does matter if the index is unique or not. The index can be used to do the check of the constraint very fast.

    3) I can see the following advantages for using nonuinque indexes to support uniqueness or primary key constraints:
    The first advantage is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables even if you use the validate option.

    Secondly, using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column already is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint.

    You also save significant space by not duplicating the index.

    Jupp

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Off topic: it is Rudy, not Ruby

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Thanks for that - it seems to make sense.

    I think I had got hold of the wrong end of the stick. I thought it was being suggested that there was a problem with the PK management rather than a new feature

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •