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

Ben,
> This excellent script from Peter takes over a minute to run, and only 
> identified RI (Referential Integrity) to one level of nesting.
>
> If I try to delete a record and get 'a foreign key constraint fails', 
> this returns instantly without any delay and to all nested levels of RI.
>
> How does MySQL do this?  Is it possible to use MySQL to find out what 
> it thinks is the problem?
InnoDB evidently has an internal map. The first query on 
information_schema takes 30 times longer than subsequent calls. It can 
be faster to parse Create Table statements, and you could do that 
recursively in an sproc.

PB
>
> Regards,
>
> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>


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