Addendum to "Check for Existence" Article

Here is another use for the technique shown in Check for Existence.

A coworker recently asserted all of our tables have both a surrogate key (an otherwise meaningless numerical value obtained from a sequence object, used to uniquely identify a row) and a business key (or keys, typically one or more unique key constraints, each consisting of one or more columns, that uniquely identify a row based on meaningful values). To test his assertion, I looked for tables that have a primary key AND at least one unique key constraint.

Find Tables that have a Primary Key AND at least One Unique Key as well

SELECT iv.table_name, iv.constraint_list
FROM (SELECT ut.table_name
            ,(SELECT CAST(
                       MULTISET(SELECT uc.constraint_name
                                FROM user_constraints uc
                                WHERE uc.table_name = ut.table_name
                                AND   uc.constraint_type IN ('P', 'U')) AS varchar2_nt
                     )
              FROM DUAL)
               constraint_list
      FROM   user_tables ut
           INNER JOIN
             user_constraints uc2
           ON uc2.table_name = ut.table_name
      WHERE uc2.constraint_type = 'P') iv -- can be exactly 0 or 1 pkey per table
WHERE CARDINALITY(iv.constraint_list) > 1
ORDER BY iv.table_name;

Out of 540 tables in our schema, I found just 175 tables having both a surrogate and business key.

I also looked for tables that don't have either a unique or primary key defined. A unique key can fill in for a primary key, and even be used as the target for a foreign key constraint. I used this query to find such tables:

Tables that Don't have a Pkey or Ukey

SELECT table_name
FROM user_tables ut
WHERE NOT EXISTS
        (SELECT 1
         FROM user_constraints uc
         WHERE uc.table_name = ut.table_name
         AND   uc.constraint_type IN ('P', 'U'));

Turns out there are 13 tables that don't have a primary or unique key. These are our Global Temporary Tables, which by definition are small and contain extremely volatile data (goes away when the session closes).