From: Ben Clewett
Date: May 11 2006 7:46am
Subject: Re: Reporting child tables referencing a parnet table
List-Archive: http://lists.mysql.com/mysql/197885
Message-Id: <4462EBDE.9090109@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>>>>
>>>
>>>
>>
>>
>>
> 
>