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!