Friday, May 20, 2011

Inserting duplicate records in Primary key field

One time I seen a duplicate entry in table primary key field,
that's strange for me or everyone, who don't know about this. But It's possible.

please follow the instructions:

1) let first us create a table:
create table testTable(
testempno number(9) primary key,
testename varchar2(50)
);

2) Insert some valid records into the table.
insert into testTablevalues(1,'test1');
insert into testTable values(2,'test2');
commit;

TESTEMPNO TESTENAME
1 test1
2 test2

3) Find out the constraint name for the primary key:
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE 'testTable' ;

CONSTRAINT_NAME
SYS_C00111110

4) Disable the constraint:
ALTER TABLE testTable DISABLE CONSTRAINT SYS_C00111110;

5) Check whether the index for the field is enabled or not:
select index_name,index_type from user_indexes where table_name like 'testTable';

It should return no rows returned. If otherwise returned drop the index.

6) Now insert the duplicate record:
INSERT INTO testTable VALUES (1,'testDuplicate');

7) Now create a non-unique index on the field:
CREATE INDEX testTable_INDEX ON testTable(testempno);

8) And at final enable the primary key constraint:
ALTER TABLE testTable ENABLE NOVALIDATE CONSTRAINT SYS_C00111110;

Now give a select * from testTable;

TESTEMPNO TESTENAME
1 test1
2 test2
1 testDuplicate

9)Now you have a enabled primary key constraint with a violated data:
SELECT CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME='testTable';

CONSTRAINT_NAME STATUS
SYS_C00111110 ENABLED

For More Info Click Here

No comments:

Split the String values with a special character in MS Flow to convert this into Array

 Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...