Primary keys are so important in databases. I keep trying to write an effective post about why they are so important. But I don’t feel I’m doing the subject justice. So let me start with a related, but shorter post.
At a number of shops, I’ve come across so many tables that did not have a primary key. Some of these were very important tables in the system! Bad, bad, bad.
After discovering so many tables that no primary key, at so many shops, I finally wrote an Oracle script to list all the tables that did not have a PK.
I named it: ltnopk.sql. Meaning, list tables, with no primary key.
/*
Script to list all the owner.tables with no PK.
Ignores SYS and SYSTEM schemas.
By Rodger Lepinsky
*/
Select tables.owner || ‘.’ ||
tables.table_name
as table_owner
From all_tables tables,
(Select
owner,
TABLE_NAME,
constraint_type,
CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = ‘P’ /* list of all tables with PK */
) constr
Where tables.owner = constr.owner (+)
And tables.table_name = constr.table_name (+)
and tables.owner <> ‘SYS’
and tables.owner <> ‘SYSTEM’
And constr.owner IS NULL
And constr.table_name IS NULL
ORDER BY 1
Note: this will also find temporary tables, which often will not have a primary key. If you want to filter the temp tables out, add this line to the WHERE clause:
And tables.DURATION is not null
Investigate away!
Find that culprit!
Reblogged this on "Andrew Sources".
Glad you like it. Thanks for the disclosure.