primary key validation only on new rows?

[anujpathak]

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

[pando]

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]

[anujpathak]

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

[anujpathak]

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

[pando]

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

[anujpathak]

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]

[pando]

well then dont use add constraint, you have to use ENABLE and DISABLE constraint

[pando]

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

[anujpathak]

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]

[denevge]

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