Very often developers try to add primary constraints on a table and they can’t do it because of duplicates in data. So question is how to quickly find the duplicates without effort.

Let’s prepare test data

create table test_tbl
(id number not null);

begin
  for i in 1..10
  loop
    insert into test_tbl values(i);
  end loop;

  insert into test_tbl values(10);

  commit;
end;
/

select * from test_tbl order by id;

        ID
----------
         1 
         2 
         3 
         4 
         5 
         6 
         7 
         8 
         9 
        10 
        10

so adding primary key raises exception

alter table test_tbl add constraint test_tbl_pk primary key(id);

SQL Error: ORA-02437: cannot validate (TOMASZ.TEST_TBL_PK) 
           - primary key violated
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate 
           values or null values.
*Action:   remove the duplicates and null values 
           before enabling a primary key.

you can look for exceptions manually

select rowid, id from (
select id, count(*) over(partition by id) cnt
from test_tbl)
where cnt > 1;

             ROWID         ID
------------------ ----------
AAASfBAAEAAAAITAAJ 10
AAASfBAAEAAAAITAAK 10

or use Oracle database to do it for you

1. Create exception table

@?/rdbms/admin/utlexcpt.sql

if you don’t have above script you can create exception table manually

create table exceptions
(
  row_id rowid,
  owner varchar2(30),
  table_name varchar2(30),
  constraint varchar2(30)
);

2. Try to turn on primary key constraint and add special clause exceptions into. It will insert records that violates PK into exception table.

alter table test_tbl add 
constraint test_tbl_pk primary key(id) 
exceptions into exceptions;

SQL Error: ORA-02437: cannot validate (TOMASZ.TEST_TBL_PK) 
           - primary key violated
02437. 00000 -  "cannot validate (%s.%s) - primary key violated"
*Cause:    attempted to validate a primary key with duplicate 
           values or null values.
*Action:   remove the duplicates and null values 
           before enabling a primary key.

3. Here you are all informations are nicely stored in exception table

select * from exceptions;

ROW_ID              OWNER   TABLE_NAME  CONSTRAINT
------------------- ------- ----------- ------------
AAASfBAAEAAAAITAAK  TOMASZ  TEST_TBL    TEST_TBL_PK
AAASfBAAEAAAAITAAJ  TOMASZ  TEST_TBL    TEST_TBL_PK

select rowid, id from test_tbl where rowid in
(select row_id from exceptions where table_name='TEST_TBL');

ROWID               ID
------------------- ---
AAASfHAAEAAAAIbAAK  10
AAASfHAAEAAAAIbAAJ  10

This trick is especially useful during migrations of data when you turn off/turn on primary key constraints.

alter table test_tbl disable constraint test_tbl_pk;

------------------------------------------
-- load huge amount of data into test_tbl
------------------------------------------
alter table test_tbl enable constraint test_tbl_pk 
exceptions into exceptions;

You can use detection also during turning on other constraints like foreign key, checks etc

1. clean duplicates in test_tbl and enable primary key

delete from test_tbl where rowid='AAASfHAAEAAAAIbAAK';

commit;

so we can enable primary key now

alter table test_tbl add constraint test_tbl_pk primary key(id);

table TEST_TBL altered.

2. create detail table

create table test_det_tbl
(id number);

insert into test_det_tbl values(20);

commit;

3. try to add foreign key to test_det_tbl

alter table test_det_tbl 
add constraint test_det_tbl_fk 
foreign key(id) references test_tbl
exceptions into exceptions;

SQL Error: ORA-02298: cannot validate (TOMASZ.TEST_DET_TBL_FK) 
           - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed 
           because the table has child records.

4. Review exception table

select * from exceptions where table_name='TEST_DET_TBL';

ROW_ID              OWNER   TABLE_NAME    CONSTRAINT 
------------------- ------- ------------- ------------
AAASfNAAEAAAAIrAAA  TOMASZ  TEST_DET_TBL  TEST_DET_TBL_FK

In this way you can verify any constraint on a table

Have a fun 🙂

Tomasz