Find just one set of columns that uniquely identify a row

If a table has PK, we need to show the list of PK columns. If the table does not have PK, we need to see if we can find one set of columns that can uniquely identify the row. One of my colleagues wrote a PL/SQL script to look at dba_constraints to do that. But I have learned that tables could have unique indexes but don't have unique constraints; on the other hand, if we create a unique constraint, we will have a unique index. For the following test tables t1,t2,t3,t4,t5:

create table t1(c1 number, c2 number, c3 number);
alter table t1 add constraint t1_pk primary key (c1, c2);

create table t2(c1 number, c2 number, c3 number);
alter table t2 add constraint t2_uk unique (c1, c2);


create table t3(c1 number, c2 number, c3 number);
create unique index t3_ix on t3(c1,c2)

create table t4(c1 number, c2 number, c3 number);
create unique index t4_ix1 on t4(c1,c2);
create unique index t4_ix2 on t4(c1,c3);

create table t5(c1 number, c2 number, c3 number);

The original script will output something like:

T5 does not have PK or any unique cols
T4 does not have PK or any unique cols
T3 does not have PK or any unique cols
T2 does not have PK but has unique cols
T2 does not have PK but has unique cols such as C1,C2
T1 has PK
T1 PK cols : C1,C2

You can see that the statements about T3 and T4 are not correct, as they have unique indexes. Below is my modified script to look at dba_indexes in stead of dba_constraints.

-- List PK columns or unique columns
-- find PK columns and if no PK, find one of the unique columns
---


set serveroutput on

declare
cntpk number;
cntuk number;
initial number;
pk varchar2(1000);
uk varchar2(1000);

-- cursor tab_cur is  select upper(source_owner) source_owner,upper(source_object_name) source_object_name from dbscrub.config_table;

cursor tab_cur is  select upper(owner) source_owner,upper(table_name) source_object_name from dba_tables 
 where owner='Vxxxx' and table_name in ('T1','T2','T3','T4', 'T5');

begin

  for tab_rec in tab_cur
  loop

  pk :='';
  uk :='';
  initial := 1;

  select count(1) into cntpk from dba_constraints where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and constraint_type = 'P';
  -- select count(1) into cntuk from dba_constraints where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and constraint_type = 'U';
  select count(1) into cntuk from dba_indexes where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and uniqueness = 'UNIQUE';

  if cntpk != 0  then

    -- update dbscrub.config_table set has_pk_uk='YES' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' has PK' );

    for i in (SELECT b.table_name, b.column_name, b.position
 FROM dba_constraints a, dba_cons_columns b
       WHERE     a.owner = b.owner
      AND a.constraint_name = b.constraint_name
      AND constraint_type = 'P'
       AND b.owner = tab_rec.source_owner
       AND b.table_name = tab_rec.source_object_name
       ORDER BY b.position)

    loop
    if initial=1 then
      pk:=i.column_name;
      initial:=initial+1;
    else
      pk:=pk||','||i.column_name;
    end if;
    end loop;

    -- update dbscrub.config_table set pk_uk_columns=pk where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' PK cols : ' || pk );

  elsif cntpk = 0 and cntuk != 0 then

   --  update dbscrub.config_table set has_pk_uk='YES' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' does not have PK but has unique cols' );

    /*  -- seems not correct
  for i in (SELECT b.table_name, b.column_name, b.position
      FROM dba_constraints a, dba_cons_columns b
     WHERE     a.owner = b.owner
    AND a.constraint_name = b.constraint_name
    AND constraint_type = 'U'
     AND b.owner = tab_rec.source_owner
     AND b.table_name = tab_rec.source_object_name
       AND rownum = 1
     ORDER BY b.position)
   */
   -- replaced by :
   /*
   for i in ( 
   SELECT b.table_name, b.column_name, b.position
      FROM ( select * from  dba_constraints a1
              where a1.owner=tab_rec.source_owner
               and  a1.table_name=tab_rec.source_object_name
               and  a1.constraint_type='U'
               and  rownum=1
           ) a,
           dba_cons_columns b
     WHERE     a.owner = b.owner
     AND       a.constraint_name = b.constraint_name
     ORDER BY b.position )
    */

   -- check dba_indexes and dba_ind_columns instead

  for i in (
  SELECT b.table_name, b.column_name, b.column_position
      FROM ( select * from  dba_indexes a1
              where a1.owner=tab_rec.source_owner
               and  a1.table_name=tab_rec.source_object_name
               and  a1.UNIQUENESS='UNIQUE'
               and  rownum=1
           ) a,
           dba_ind_columns b
     WHERE     a.owner = b.index_owner
     AND       a.index_name = b.index_name
  ORDER BY b.column_position )
  
  loop
  if initial=1 then
  uk:=i.column_name;
  initial:=initial+1;
  else
  uk:=uk||','||i.column_name;
  end if;
  end loop;

  --  update dbscrub.config_table set pk_uk_columns=uk where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' do not have PK but has unique cols such as ' || uk );

  else
   

   --    update dbscrub.config_table set has_pk_uk='NO' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;

    dbms_output.put_line ( tab_rec.source_object_name  || ' does not have PK or any unique cols');

  end if;
  end loop;
  commit;
end;
/

The modified script's output looks like:

T5 does not have PK or any unique cols
T4 does not have PK but has unique cols
T4 does not have PK but has unique cols such as C1,C2
T3 does not have PK but has unique cols
T3 does not have PK but has unique cols such as C1,C2
T2 does not have PK but has unique cols
T2 does not have PK but has unique cols such as C1,C2
T1 has PK
T1 PK cols : C1,C2

BTW, the need for this script comes from our tasks of configuring Oracle GoldenGate.