Wednesday, July 29, 2009

How to find all tables without primary key

Getting a list of all tables that don't have a primary key is occasionally useful when you need to update a legacy schema or repair someone else's database "design". There are a few recipes out there that peek into the PostgreSQL system catalogs, but here is a way that uses the information schema:
SELECT table_catalog, 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');
The last line is useful because many predefined tables don't have primary keys.

Since you're using the standardized information schema, this query should be portable to other SQL database systems. The identical query works on MySQL, for example.

8 comments:

  1. One thing to note is that this will only tell you about tables that you have some privilege on. So if you don't run it as superuser, you might miss some stuff.

    ReplyDelete
  2. This slight modification gets the tables that have no kind of uniqueness on
    them, whether it's called a primary key or not :)

    SELECT table_catalog, 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 NOT IN 'PRIMARY KEY', 'UNIQUE'))
    AND table_schema NOT IN ('information_schema', 'pg_catalog');

    ReplyDelete
  3. Checking further, the above queries find VIEWs along with TABLEs, at least in PostgreSQL, so the following should be better:

    SELECT table_catalog, 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 NOT IN ('PRIMARY KEY', 'UNIQUE'))
    AND table_schema NOT IN ('information_schema', 'pg_catalog')
    AND table_type = 'BASE TABLE';

    ReplyDelete
  4. For me David's queries do not list a table whose (only) constraint is a foreign key reference.

    ReplyDelete
  5. How about this one?

    select c.oid::regclass from pg_class c join pg_namespace n on (relnamespace=n.oid) where relkind = 'r' and nspname in ('schema_list') except select indrelid::regclass from pg_index where indisunique ;

    ReplyDelete
  6. @denish got one that goes strictly from the information schema?

    ReplyDelete
  7. Nice!
    Thanks for useful information.

    I think including "engine" column would be useful, so it turns to

    SELECT table_catalog, table_schema, table_name, engine
    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');

    ReplyDelete
    Replies
    1. There is no engine column in that view in standard SQL.

      Delete