Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing primary and foreign keys?

PostgreSQL 8.4 and 9.x versions

share|improve this question

2 Answers

up vote 5 down vote accepted

To list all tables without a primary key, you can use this:

select tbl.table_schema, 
       tbl.table_name
from information_schema.tables tbl
where table_type = 'BASE TABLE'
  and table_schema not in ('pg_catalog', 'information_schema')
  and not exists (select 1 
                  from information_schema.key_column_usage kcu
                  where kcu.table_name = tbl.table_name 
                    and kcu.table_schema = tbl.table_schema)

It's not clear to me if you want to find tables that are not referencing any other tables or tables that are not referenced by other tables. But both things can be obtained by querying information_schema.referential_constraints in a similar way as the above query.

share|improve this answer
perfect! thanks – Phill Pafford Dec 6 '12 at 15:45
up vote 0 down vote

Another one query to get all tables without primary keys:

SELECT table_schema || '.' || table_name
FROM information_schema.tables
WHERE
    (table_catalog, table_schema, table_name) NOT IN (
        SELECT table_catalog, table_schema, table_name
        FROM information_schema.table_constraints
        WHERE constraint_type = 'PRIMARY KEY') AND
    table_schema NOT IN ('information_schema', 'pg_catalog', 'pgq', 'londiste');

Unfortunately PostgreSQL has no idea what tables are supposed to have FKs. So it is manual work, you will need to look over all the tables and find such tables by yourself.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.