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).
No comments:
Post a Comment