Peter,
I see... I was thinking 'db' might have been a system table name or
something...
This works perfectly, thanks.
Ben
Peter Brawley wrote:
> Ben Clewett wrote:
>> 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)
> Indeed, you didn't substitute your 'db' and 'table' values.
>
> PB
>
> -----
>>
>> 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
>>>>
>>>
>>>
>>
>>
>>
>
>
