From: Peter Brawley
Date: May 10 2006 4:52pm
Subject: Re: Reporting child tables referencing a parnet table
List-Archive: http://lists.mysql.com/mysql/197857
Message-Id: <44621A61.8000608@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006