4

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

10

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.

1

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.

0

Here is the query for FKs.

SELECT table_name
FROM information_schema.tables
WHERE table_name NOT IN (
   SELECT distinct table_name
        FROM information_schema.table_constraints
        WHERE constraint_type = 'FOREIGN KEY' and table_schema ='xxxxx') AND
    table_schema ='xxxxx'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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