I had a mail from my friend describing the way to inserting duplicate value in a primary key field. I have reproduced it below:
First let us create a table:
create table test100
(
empno number(9) primary key,
ename varchar2(50)
);
Insert some valid records into the table:
insert into test100 values(1,'Sachin');
insert into test100 values(2,'Saurav');
commit;
EMPNO ENAME
1 Sachin
2 Saurav
Find out the constraint name for the primary key:
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE 'TEST100' ;
CONSTRAINT_NAME
SYS_C00249950
Disable the constraint:
ALTER TABLE TEST100 DISABLE CONSTRAINT SYS_C00249950;
Check whether the index for the field is enabled or not:
select index_name,index_type from user_indexes where table_name like 'TEST100';
It should return no rows returned. If otherwise returned drop the index.
Now insert the duplicate record:
INSERT INTO TEST100 VALUES (1,'Ganguly');
Now create a non-unique index on the field:
CREATE INDEX TEST100_INDEX ON TEST100(EMPNO);
And at final enable the primary key constraint:
ALTER TABLE TEST100 ENABLE NOVALIDATE CONSTRAINT SYS_C00249950;
Now give a select * from test100;
EMPNO ENAME
1 Sachin
2 Saurav
1 Ganguly
Now you have a enabled primary key constraint with a violated data:
SELECT CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST100';
CONSTRAINT_NAME STATUS
SYS_C00249950 ENABLED
Can anybody give suggestions as to why Oracle has given the NOVALIDATE clause while enabling the constraint?
