Programowanie

W jaki sposób sprawdzić które tabele mają zdefiniowane klucze obce do tabeli users.

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,   -- <<-- Nazwa potrzebna do skasowania 
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'users'\G

+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+      
| articles   | user_id     | user_key        | users                 | id                     |
| bookmarks  | user_id     | ub_key          | users                 | id                     |
+------------+-------------+-----------------+-----------------------+------------------------+

Druga wersja

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL; 

+--------------+---------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | CONSTRAINT_NAME |
+--------------+---------------+-------------+-----------------+
| intranet     | articles      | user_id     | user_key        |
| intranet     | articles_tags | article_id  | article_key     |
| intranet     | articles_tags | tag_id      | tag_key         |
| intranet     | bookmarks     | user_id     | ub_key          |
+--------------+---------------+-------------+-----------------+