More on Database Design Mistakes to Avoid
Related to referential integrity, in addition to systems that do not have primary keys, there are database “designs” where there were no foreign keys! Much like a Cobol system.
Here is an Oracle script to find the tables that don’t have foreign keys. Tables that are neither a parent table, or a child table.
I named it: ltnofk.sql.
Meaning, list tables, with no foreign keys.
/*
Script to find all the tables with no FK.
They have no parents, and no children.
Excludes SYS and SYSTEM schemas.
By Rodger Lepinsky
*/
/* all tables */
SELECT D1.OWNER || ‘.’ || D1.TABLE_NAME
AS TABLE_OWNER
FROM ALL_TABLES D1
WHERE D1.OWNER <> ‘SYS’
AND D1.OWNER <> ‘SYSTEM’
GROUP BY D1.OWNER || ‘.’ || D1.TABLE_NAME
MINUS
/* TABLES THAT HAVE PARENTS, ARE CHILDREN */
SELECT A1.OWNER || ‘.’ || A1.TABLE_NAME
AS TABLE_OWNER
FROM ALL_CONSTRAINTS A1,
ALL_CONSTRAINTS B1,
ALL_CONS_COLUMNS C1
WHERE A1.CONSTRAINT_NAME = B1.R_CONSTRAINT_NAME
AND A1.OWNER=B1.R_OWNER
AND A1.CONSTRAINT_TYPE IN (‘P’, ‘U’)
AND C1.TABLE_NAME=B1.TABLE_NAME
AND C1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME
AND C1.OWNER = B1.OWNER
AND C1.OWNER <> ‘SYS’
AND C1.OWNER <> ‘SYSTEM’
GROUP BY A1.OWNER || ‘.’ || A1.TABLE_NAME
MINUS
/* TABLES THAT HAVE CHILDREN, ARE PARENTS */
SELECT B1.OWNER || ‘.’ || B1.TABLE_NAME
AS TABLE_OWNER
FROM ALL_CONSTRAINTS B1,
ALL_CONS_COLUMNS C1
WHERE B1.R_CONSTRAINT_NAME = C1.CONSTRAINT_NAME
AND B1.R_OWNER=C1.OWNER
AND C1.OWNER <> ‘SYS’
AND C1.OWNER <> ‘SYSTEM’
AND B1.CONSTRAINT_TYPE = ‘R’
GROUP BY B1.OWNER || ‘.’ || B1.TABLE_NAME
/
Again, this will also find temporary tables, which usually are stand alone, with no FK. If you want to filter the temp tables out, add this line to the first WHERE clause:
And tables.DURATION is not null
Sometimes you might find an error_log table, or staging tables for data loads.
Investigate away!