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.