0

I can easily find tables that do not contain a PK from information_schema, but I'm having a tough time writing the logic to find tables that might contain neither a PK nor a Composite Keys (via two FKs).

What I need is a for loop that says for each table if there is no instance of a PRIMARY KEY constraint_type then select it where there are at least 2 records of it where constraint_type is FOREIGN KEY.

To be honest I'm not even sure where to begin. It's been a long while since I wrote a FOR loop in SQL.

EDIT: to be clear this is coming from the need to find tables without a Primary Key. Oracle support has advised that a lack of a PK makes it challenging for replication to keep up. I use Hibernate and some tables are "join tables" in Hibernate parlance, whereby there is not specific PK, but instead the table is a composite of two FKs (a Composite Key). What I want to find is tables that have NEITHER. I want to identify where either Hibernate is failing to create PKs or CKs (via two FKs) or my developers are simply getting sloppy and not specifying PKs.

  • with no primary key OR no composite key This means that the table which have PK but have not CK must "be found", is it? and the same when it have CK and have not PK? In other words, you need all tables except ones which have both PK and CK? – Akina Mar 1 at 4:30
  • I need all tables which have neither a PK nor a CK. – tadamhicks Mar 1 at 23:43
0

You don't need to loop, and I don't understand what is "...CK (via two FK)". You can use the standard information_schema views to get all the information you need:

Table Constraints

Tables

The first will show you all constraints, and the second all tables. Any table which is in tables, but not in constraints, has none. You can use a simple EXCEPT operator:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Or use EXISTS:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES AS T
WHERE NOT EXISTS (SELECT NULL 
                  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                  WHERE T.TABLE_SCHEMA = TC.TABLE_SCHEMA
                        AND
                        T.TABLE_NAME = TC.TABLE_NAME
                  );

You can build whatever logic you want around it with WHERE clauses for the constraints table.

HTH

  • MySQL knows nothing about EXCEPT. – Akina Mar 1 at 4:27
  • Except for MariaDB-10.3+ :-) – danblack Mar 1 at 5:06
0

This will discover which tables have composite indexes:

USE information_schema;
SELECT a.table_schema, a.table_name
    FROM tables a
    WHERE NOT EXISTS ( SELECT 1 
                FROM key_column_usage 
                WHERE table_schema = a.table_schema
                  AND table_name = a.table_name
                  AND ordinal_position > 1 );

(I do not see the relevance of FOREIGN_KEYs in your question. You can have a composite index that involves zero, one, more multiple foreign keys.)

A simpler query can check for the absence of any constraint_name = 'PRIMARY'.

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.