primary key validation only on new rows?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: primary key validation only on new rows?

  1. #1
    Join Date
    Nov 2000
    Posts
    33

    Lightbulb

    I have a table where in a column i have non unique code
    what i want is to implement primary key on this column for only new rows inserted not on already existing non unique codes in that column.


    Any light on it...

    Thanks in advance



  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    not sure what are your requirements but in datawarehouese you can play around with alter table xxx enable constraint yyy novalidate (by default it's validate) but that is to use after a batch data load not for everyday OLTP databases

    [Edited by pando on 04-15-2002 at 06:17 AM]

  3. #3
    Join Date
    Nov 2000
    Posts
    33

    i tried novalidate but this doesn't work

    novalidate doesn't work i tried as follows :

    SQL> create table aa (code char(2) constraint pk_1 primary key,name char

    Table created.

    SQL> insert into aa values('11','anuj1') ;

    1 row created.

    SQL> commit ;

    Commit complete.

    SQL> insert into aa values('11','anuj2') ;
    insert into aa values('11','anuj2')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.PK_1) violated


    SQL> alter table aa drop constraint pk_1 ;

    Table altered.

    SQL> insert into aa values('11','anuj2') ;

    1 row created.

    SQL> commit ;

    Commit complete.

    1 alter table aa add constraint
    2* pk_1 primary key (code) novalidate
    SQL> /
    alter table aa add constraint
    *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.PK_1) - primary key violated


  4. #4
    Join Date
    Nov 2000
    Posts
    33

    novalidate does not work. i tried as follows :

    novalidate does not work. i tried as follows :

    SQL> create table aa (code char(2) constraint pk_1 primary key,name char

    Table created.

    SQL> insert into aa values('11','anuj1') ;

    1 row created.

    SQL> commit ;

    Commit complete.

    SQL> insert into aa values('11','anuj2') ;
    insert into aa values('11','anuj2')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.PK_1) violated


    SQL> alter table aa drop constraint pk_1 ;

    Table altered.

    SQL> insert into aa values('11','anuj2') ;

    1 row created.

    SQL> commit ;

    Commit complete.

    1 alter table aa add constraint
    2* pk_1 primary key (code) novalidate
    SQL> /
    alter table aa add constraint
    *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.PK_1) - primary key violated


  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it{s used in enabling and disabling constraints, I dont think it works with adding constraints, also it works for 8i so if you are using lower versions then forget it

  6. #6
    Join Date
    Nov 2000
    Posts
    33

    I am using oracle8i & output on enable & disable

    I am using oracle8i I tried as u said by enable & disable process is written below :

    SQL> select * from v$version ;

    BANNER
    ---------------------------------------------------------
    Oracle8i Release 8.1.5.0.0 - Production
    PL/SQL Release 8.1.5.0.0 - Production
    CORE Version 8.1.5.0.0 - Production
    TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
    NLSRTL Version 3.4.0.0.0 - Production


    SQL> create table aa (code char(2) constraint pk_1 primary key,name char(20) ) ;

    Table created.

    SQL> insert into aa values('11','anuj1') ;

    1 row created.

    SQL> insert into aa values('11','anuj2') ;
    insert into aa values('11','anuj2')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.PK_1) violated


    1* alter table aa disable constraint pk_1
    SQL> /

    Table altered.

    SQL> insert into aa values('11','anuj2') ;

    1 row created.

    SQL> commit ;

    Commit complete.

    SQL> alter table aa enable constraint pk_1 ;
    alter table aa enable constraint pk_1
    *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.PK_1) - primary key violated




    [Edited by anujpathak on 04-15-2002 at 07:14 AM]

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well then dont use add constraint, you have to use ENABLE and DISABLE constraint

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: I am using oracle8i & output on enable & disable

    Originally posted by anujpathak

    SQL> alter table aa enable constraint pk_1 ;
    alter table aa enable constraint pk_1
    *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.PK_1) - primary key violated
    [Edited by anujpathak on 04-15-2002 at 07:14 AM]
    Hmm I thought I mentioned use novalidate clause in my previous post... not that hard to read man just have a closer look

  9. #9
    Join Date
    Nov 2000
    Posts
    33

    tried by enable & disable with novalidate

    output with enable & disable of constraint and novalidate
    if i am wrong on syntex please let me know


    SQL> create table aa (code char(2) constraint pk_1 primary key,name char(20) )
    2 ;

    Table created.

    SQL> insert into aa values ('11','anuj1') ;

    1 row created.

    SQL> commit ;

    Commit complete.

    SQL> insert into aa values ('11','anuj2') ;
    insert into aa values ('11','anuj2')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.PK_1) violated


    SQL> alter table aa disable constraint pk_1 ;

    Table altered.

    SQL> insert into aa values ('11','anuj2') ;

    1 row created.

    SQL> alter table aa enable constraint pk_1 novalidate ;
    alter table aa enable constraint pk_1 novalidate
    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended




    [Edited by anujpathak on 04-15-2002 at 08:05 AM]

  10. #10
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    It works when we first create a non-unique index on the PK column.

    This example is made on 8.1.6.2

    > create table aa ( a number );
    Table created.

    > insert into aa ( a ) values ( 1 ) ;
    1 row created.

    > /
    1 row created.

    > insert into aa ( a ) values (2 ) ;
    1 row created.

    > create index aa_i on aa ( a );
    Index created.

    > alter table aa add constraint aa_pk primary key ( a ) novalidate ;
    Table altered.

    > select a from aa;
    A
    ----------
    1
    1
    2
    3 rows selected.

    > insert into aa ( a ) values ( 2 );
    insert into aa ( a ) values ( 2 )
    *
    ERROR at line 1:
    ORA-00001: unique constraint (DWH.AA_PK) violated

    > insert into aa ( a ) values ( 3 ) ;
    1 row created.

    > /
    insert into aa ( a ) values ( 3 )
    *
    ERROR at line 1:
    ORA-00001: unique constraint (DWH.AA_PK) violated

    Regards
    Gert

Posting Permissions

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


Click Here to Expand Forum to Full Width