Menu
I'm working on implementing a function to prevent removal of certain elements in a database (through the front end) if they have other items associated with them in other tables. Otherwise those other tables are looking for keys that aren't there.
If you understood that my hat is off to you.
I have many sets of tables to look through and need either a SQL query or a MySQL Workbench feature that can tell me, on entry of the primary key (column name, not actual value), if that key is used as a foreign key somewhere else.
Otherwise if anyone knows an offhand workaround, that would be great too!
255
To see foreign key relationships of a table: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db_name' AND REFERENCED_TABLE_NAME = 'table_name';
If you mean "can foreign key 'refer' to a primary key in the same table?", the answer is a firm yes as some replied.
If we want to know the table's primary keys and foreign keys. We can simply use an “information_schema. key_column_usage” view, this view will return all of the table's foreign keys and primary keys.
SELECT
table_name, column_name
FROM
information_schema.key_column_usage
WHERE
referenced_table_name = '<table>'
and referenced_column_name = '<primary key column>'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
