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?
This clause will be useful for data uploading where constraints may be violated for already existing data.
ReplyDeleteAnd,
ReplyDeletethere 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
how to get the info of those records which have the same primary key?
ReplyDeleteRegards,
Mano
(mano.loganathan@gamil.com)