In mysql, find all instances of a named column that are not foreign keys

Question

This one has had me trumped for a while!

I want to run a query to retrieve all instances of a column (from information_schema, I guess) that ARE or ARE NOT foreign keys.

Now, let me back off a little and explain my real need. I have a rather large database dealing with company information. Now, I want to be able to delete (or update) a customer record, and have all related foreign keys affected by CASCADE. Now, if the foreign key constraints are not duly set everywhere, then a few records might be orphaned.

So, I want to first query to get a bird's eye view of my column. Now let's assume that all instances of this column have the same name "mycol".

I've taken note of the table REFERENTIAL_CONSTRAINTS, but got intimidated ;). One immediate constraint (pardon the pun) I've seen is: the field CONSTRAINT_NAME is populated with names like customer_ibfk_1, since the naming of FK constraints on this project was left for mysql to handle as default. This table has no field for "REFERENCED_COLUMN", and so I'm stumped.

Any help please!

Answer 1

Here's what I eventually came up with. Of course, I'm open to input/refinements from the DBA grandfathers around here! But for now, this approach is giving me the info I need.

USE INFORMATION_SCHEMA;

Get all tables that have at least one foreign key referencing a column in "mytable"

SELECT TABLE_NAME, COUNT(*) AS reference_count 
FROM REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = 'mydb' 
    AND REFERENCED_TABLE_NAME = 'mytable' GROUP BY TABLE_NAME;

Get all tables that have at least one foreign key on a column named "mycolumn"

SELECT TABLE_NAME 
FROM KEY_COLUMN_USAGE 
WHERE CONSTRAINT_SCHEMA = 'mydb' 
    AND COLUMN_NAME = 'mycolumn' 
    AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

Get all tables that have ANY COLUMN named "mycolumn". The subquery in the WHERE clause AND (SELECT Table_Type FROM TABLES WHERE table_schema = 'mydb' AND TABLE_NAME = COLUMNS.TABLE_NAME) is necessary, otherwise it would return also any VIEW having a column named "mycolumn"

SELECT TABLE_NAME 
FROM COLUMNS 
WHERE 
    TABLE_SCHEMA = 'mydb' 
    AND COLUMN_NAME = 'mycolumn' 
    AND (SELECT Table_Type 
        FROM TABLES WHERE table_schema = 'mydb' 
        AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE';

Now, using the 2 previous queries, we can obtain any tables having a column named "mycolumn" but in which "mycolumn" is NOT A FOREIGN KEY. Note that this filtering will not respect a PRIMARY KEY, and will also return any table where "mycolumn" is a PRIMARY KEY.

SELECT TABLE_NAME 
FROM COLUMNS 
WHERE TABLE_SCHEMA = 'mydb' 
    AND COLUMN_NAME = 'mycolumn' 
    AND (SELECT Table_Type 
        FROM TABLES 
        WHERE table_schema = 'mydb' 
            AND TABLE_NAME = COLUMNS.TABLE_NAME) = 'BASE TABLE' 
    AND TABLE_NAME NOT IN
        (SELECT TABLE_NAME 
        FROM KEY_COLUMN_USAGE 
        WHERE CONSTRAINT_SCHEMA = 'mydb' 
            AND COLUMN_NAME = 'mycolumn' 
            AND REFERENCED_TABLE_SCHEMA IS NOT NULL
        );

Answer 2

Foreign key can be multicolumn, so having single value like "REFERENCED_COLUMN" is not possible. Instead the table seems to refer to index covering the referenced column(s), you could try to check these.

But there seems to be better possibility with information_schema.KEY_COLUMN_USAGE, which lists directly table/column name and for it referenced table/column name. I tried to test and show it in this fiddle http://sqlfiddle.com/#!9/3d96f/2

If you do query of all instances of your column name, you can then left-join it to this by table and column to see, if FK exists or if you get null.