Inserting duplicate records in Primary key field

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?

3 comments :

  1. This clause will be useful for data uploading where constraints may be violated for already existing data.

    ReplyDelete
  2. And,
    there is not "NOVALIDATE" option it is total four types.
    1. enable validate
    2. enable novalidate
    3. disable validate
    4. disable novalidate
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/data_int.htm#CNCPT622

    Regards
    Taj

    ReplyDelete
  3. how to get the info of those records which have the same primary key?



    Regards,
    Mano
    (mano.loganathan@gamil.com)

    ReplyDelete