Finding MySQL Tables with no primary key OR no composite key

Question

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.

Answer 1

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

Answer 2

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'.