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
Printable View
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
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]
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
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
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
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]
well then dont use add constraint, you have to use ENABLE and DISABLE constraint
Hmm I thought I mentioned use novalidate clause in my previous post... not that hard to read man just have a closer lookQuote:
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]
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]
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