Bianca Bianca - 12 days ago 5
MySQL Question

how to know if table is parent table in mysql

I tried this query

SELECT
CONSTRAINT_CATALOG AS constraintCatalog,
CONSTRAINT_SCHEMA AS constraintSchema,
CONSTRAINT_NAME AS constraintName,
TABLE_CATALOG AS tableCatalog,
TABLE_SCHEMA AS tableSchema,
TABLE_NAME AS tableName,
COLUMN_NAME AS columnName,
ORDINAL_POSITION AS ordinalPosition,
POSITION_IN_UNIQUE_CONSTRAINT AS positionUniqueConstraint,
REFERENCED_TABLE_SCHEMA AS referencedTableSchema,
REFERENCED_TABLE_NAME AS referencedTableName,
REFERENCED_COLUMN_NAME AS referencedColumn

FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE
TABLE_NAME = 'table_name'


but the problem I only got results for detecting foreign keys.
Is there any way to detect parent and child of the table? because we need to display that in User Interface. Thank you

Answer

Did you try this:

SELECT
  FK.referenced_table_name parent_table,
  FK.table_name child_table,
  FK.constraint_name as FK_name
FROM
  information_schema.KEY_COLUMN_USAGE FK
WHERE
  FK.referenced_table_name IS NOT NULL

Ref: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html