2

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!

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
        );
  • In the absence of other (hopefully superior) answers, could I ask that this comment be a notice that I will soon be accepting my own answer. Thanks all. – Ifedi Okonkwo May 12 '15 at 15:08
0

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.

  • Thanks @user1786423 for the answer. However, it was easier for me to dig through the mud in I_S than to wrap my head around your suggestions, though I suspect that what I came up with is generally in line with what you were trying to say. – Ifedi Okonkwo May 12 '15 at 15:06

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.