Suppose we create 2 tables and insert data in parent table as follow:
query:
sql>>
--start of query
create table parent
query:
sql>>
--start of query
create table parent
(
a number(2),
b number(2)
);
create table child
(
c number(2),
d number(2)
);
insert into parent values(1,1);
insert into parent values(2,2);
insert into parent values(3,3);
insert into parent values(1,1);
--end of query
output>>
Table PARENT created.
Table CHILD created.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
Here we have inserted duplicates into parent table and if we want to apply primary key on column 'a' of parent table it won't accept because it has duplicate keys so we need to remove duplicates first .
sql>>
--start of query
Alter table parent add constraint pk_parent primary key (a);
--end of query
output>> Error Cause:attempted to validate a primary key with duplicate values or
null values.
So what to do??
We have 2 alternatives 1) Use novalidate clause
2) Remove duplicates and then apply primary key
Alternative1)We can use novalidate clause if we want that from now onwards whatever we insert into table is not same as content of table itself. i.e if table is currently having duplicate, we are ok with it but we want that incoming data doesn't have any duplicates ,that means we want primary key constraint to be evaluated for data which we are going to insert from now onwards and hence here we can use novalidate clause .
sql>>
--start of query
Alter table parent add constraint pk_parent primary key (a) enable novalidate ;
--end of query
output>> Error:attempted to validate a primary key with duplicate values or null
values.
Here also we are getting error because table contains duplicate.But we are using novalidate clause ,which means: check constraint for existing data not for persisting data. Reason is that oracle is trying to create a unique index for primary key we are applying (in this case for column 'a') by default and hence we are getting error. But we can ask oracle to use non-unique index by explicitly mentioning that in our query like this.
sql>>
--start of query
Alter table parent add constraint pk_parent primary key (a) using index (create index pk_parent on parent(a)) enable novalidate ;
--end of query
output>> table PARENT altered.
Success!! it works. Lets insert some keys in parent table.
sql>>
--start of query
insert into parent values(4,2);
--end of query
output>> 1 rows inserted.
Now if we try to insert some duplicate or null key in column 'a' of parent, it won't accept.
Lets try inserting.
sql>>
--start of query
insert into parent values(1,2);
--end of query
output>> Error unique constraint violated
Here we get error because column 'a' of parent table already has key 1 and primary constraint is in effect.
Content of table parent is:
sql>>
--start of query
Select * from parent;
--end of query
output>>
A B
---------- ----------
1 1
2 2
3 3
1 1
4 2
So by using novalidate clause me have made sure that no duplicates get inserted after applying primary key but table can have previous duplicate keys. Notice that for that we need to create non-unique index explicitly because oracle creates unique index by default.
Now lets talk about alternative 2)Removing duplicates: For this we can ask oracle to help us know which duplicates are violating constraints. In effect we need to provide oracle with table in which oracle will insert rowid of rows violating constraint whose columns are something like below:
First drop primary constraint to explore alternative 2.
sql>>
--start of query
Alter table parent drop constraint pk_table;
--end of query
output>> Table PARENT altered.
sql>>
--start of query
create table excp_table
(
row_id rowid,
table_owner varchar2(30),
table_name varchar2(30),
violated_constraint_name varchar2(30)
);
--end of query
output>>
table EXCP_TABLE created.
Now we can use this table to apply primary key.
sql>>
--start of query
Alter table parent enable constraint pk_parent exceptions into excp_table;
--end of query
output>>
Error:primary key violated.
This is ok as we can see now our excp_table contain duplicates which were causing trouble.
sql>>
--start of query
select * from excp_table;
--end of query
output>>
ROW_ID TABLE_OWNER TABLE_NAME VIOLATED_CONSTRAINT_NAME
------ ------------------------------ ------------------------------ ------------------------------
AAASfC SYSTEM PARENT PK_PARENT
AABAAA
VVxAAD
AAASfC SYSTEM PARENT PK_PARENT
AABAAA
VVxAAA
Here by this we get all duplicates i.e if key 1 is present 2 times , we get 2 rows in excp_table.
Here is the same case as we are getting 2 rows in excp_table because key 1 is present 2 times.
Now we can delete duplicates using excp_table as:
sql>>
--start of query
Delete parent where rowid in (select row_id from excp_table);
--end of query
output>>
2 rows deleted.
Now lets apply primary key on column 'a' of parent table.
sql>>
--start of query
alter table parent add constraint pk_parent primary key(a);
--end of query
output>>
table PARENT altered.
Success!! we are able to apply primary key because it doesn't contains any duplicates.
Now lets disable this contraint and add some duplicates.
sql>>
--start of query
alter table parent disable constraint pk_parent ;
insert into parent values(1,1);
insert into parent values(1,2);
--end of query
output>>
table PARENT altered.
1 row inserted.
1 row inserted.
Here we are able to insert duplicates because we disabled primary key constraint. Now when we try to enable this constraint we get error saying unique constraint violated. Constraint can be enabled as follows:
sql>>
--start of query
alter table parent enable constraint pk_parent ;
--end of query
output>>
Error cause: attempted to validate a primary key with duplicate values or null
values.
So here also we can use alternative 2 mentioned above.So it was all about novalidate clause from myside, thank you. I will talk about deferrable clause later.
If any doubt you can ask freely in comments.
