Unfortunately I get:
mysql> SELECT
-> c.table_schema,u.table_name,u.column_name,u.referenced_column_name
-> FROM information_schema.table_constraints AS c
-> INNER JOIN information_schema.key_column_usage AS u
-> USING( constraint_schema, constraint_name )
-> WHERE c.constraint_type = 'FOREIGN KEY'
-> AND u.referenced_table_schema='db'
-> AND u.referenced_table_name = 'table'
-> ORDER BY c.table_schema,u.table_name;
Empty set (27.86 sec)
mysql> SELECT version();
+-----------------+
| version() |
+-----------------+
| 5.1.6-alpha-log |
+-----------------+
But thanks, I think I can work with this and get the information I want.
Regards,
Ben.
Peter Brawley wrote:
> Ben,:
>> Dear MySQL,
>>
>> Can you please tell me if there is a way of listing all child tables
>> which have a foreign key reference to a parent?
> Find children of db.table:
>
> SELECT
> c.table_schema,u.table_name,u.column_name,u.referenced_column_name
> FROM information_schema.table_constraints AS c
> INNER JOIN information_schema.key_column_usage AS u
> USING( constraint_schema, constraint_name )
> WHERE c.constraint_type = 'FOREIGN KEY'
> AND u.referenced_table_schema='db'
> AND u.referenced_table_name = 'table'
> ORDER BY c.table_schema,u.table_name;
>
> PB
>
> -----
>
>>
>> Therefore I can find and delete a child row, then delete the parent
>> without getting:
>>
>> "a foreign key constraint fails"
>>
>> Regards,
>>
>> Ben Clewett
>>
>
>
