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.

DDL for CONSTRAINTS and DEFAULTS

When using a CTAS to create a table from an existing table, the new table won't have all the constraints as the exsiting table except that the NOT NULL constraints are preserved. Below is an example showing a CHECK constraint is not presented in a table created from CTAS:

TESTDB> create table t (id number not null, val number check (val > 5));
 
Table created.
 
 
TESTDB> insert into t values(1,7);
 
1 row created.
 
TESTDB> commit;
 
Commit complete.
 
TESTDB> insert into t values (2,4);
insert into t values (2,4)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$MYUSRID.SYS_C001214102) violated
 
 
TESTDB> desc t;
 Name                  Null?    Type     
 --------------------- -------- --------
 ID                    NOT NULL NUMBER
 VAL                            NUMBER
 
TESTDB> create table t1 as select * from t;
 
Table created.
 
TESTDB> desc t1;
 Name                 Null?    Type
 -------------------- -------- ----------
 ID                   NOT NULL NUMBER
 VAL                           NUMBER
 
TESTDB> insert into t1 values (1, 4);
 
1 row created.
 


TESTDB> ;
  1* select owner, constraint_name, constraint_type, table_name, search_condition from user_constraints
TESTDB> /
 
OWNER        CONSTRAINT_NAME                C TABLE_NAME SEARCH_CONDITION
------------ ------------------------------ - ---------- ------------------------------
OPS$MYUSRID  SYS_C001214102                 C T          val > 5
OPS$MYUSRID  SYS_C001214101                 C T          "ID" IS NOT NULL
OPS$MYUSRID  SYS_C001214103                 C T1         "ID" IS NOT NULL

To create the constraints in the new table, I thus faced a task to extract all the DDL about constraints from the existing table. The approach I adopted after several tests was as follows.

1. First, I created a table my_constraints to have CLOB datatype for search_conditon column since we can not use LIKE operater on a LONG type column.

create table my_constraints ( 
    owner varchar2(30)
   , constraint_name varchar2(30)
   , constraint_type varchar2(1)
   , table_name varchar2(30)
   , search_condition clob
   ) ;

   insert into my_constraints
   select owner, constraint_name, constraint_type, table_name, to_lob(search_condition)
     from dba_constraints 
   where owner =&owner ;

2. For PK, UK and check constraints ( excluding NOT NULL), I used the following command to extract the DDL:

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', t.constraint_name , t.owner ) ddl_string
  from my_constraints t
 where table_name = upper('&table_name')
  and  owner=upper('&owner')
and constraint_type in ('P', 'U','C')
and ( search_condition is null
      or ( search is not null and search_condition not like '%NOT NULL')
    )
;

3. For FK constraints, I used the following command to extract the DDL

SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
  FROM (SELECT DISTINCT b.table_name, b.owner
           from dba_constraints b
           where b.constraint_type = 'R'
           AND b.owner = upper('&owner')
           AND b.table_name = upper('&table_name')) ;

CTAS also does not preserve DEFAULT values of columns. I used the following to obtain the 'ALTER TABLE' statement for DAFAULTS:

BEGIN
        FOR r IN (
             select owner, table_name, column_name, data_type, data_default
               from dba_tab_columns
             where owner = upper('&owner')
               and table_name = upper('&table_name')
               and data_default is not null
        )
        LOOP
                DBMS_OUTPUT.PUT_LINE
                ( 'ALTER TABLE ' || r.owner || '.' || r.table_name  ||
                 ' modify  ' ||r.column_name ||
                 ' default ' || r.data_default || ';');
        END LOOP;
END;
/

The reason we need to use PL/SQL instead of SQL is that DATA_DEFAULT column is LONG type. We can not use '||' with LONG type column.

Truncate a parent table

I overlooked the operations required to truncate a parent table. I thought I only need to
truncate all of its child tables first, then truncate itself. The following test demonstrated
that constraints have to be taken care of in addition:


SQL> @fk_child
Enter value for 1: z9xxx94
Enter value for 2: p
----- Children of z9xxx94 p -------

Owner Table Name Constraint Name Ref Constraint Name Status
-------------- ----------------- ------------------ ------------------------ --------
Z9xxx94 C C_FK P_PK ENABLED



SQL> truncate table c;

Table truncated.

SQL> truncate table p;
truncate table p
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


We have to disable child table FK constraint first:


SQL> alter table c modify constraint C_FK disable;

Table altered.

SQL> truncate table p;

Table truncated.

SQL> alter table c modify constraint C_FK enable;

Table altered.

SQL> select count(*) from c;

COUNT(*)
----------
0

SQL> select count(*) from p;

COUNT(*)
----------
0