List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:May 11 2006 7:51am
Subject:Re: Reporting child tables referencing a parnet table
View as plain text  
MySQL,

Another general comment, I'm interested in knowing....

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?

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


-- 
Ben Clewett
+44(0)1923 460000
Project Manager
Road Tech Computer Systems Ltd
http://www.roadrunner.uk.com
Thread
Reporting child tables referencing a parnet tableBen Clewett10 May
  • Re: Reporting child tables referencing a parnet tablePeter Brawley10 May
    • Re: Reporting child tables referencing a parnet tableBen Clewett10 May
      • Re: Reporting child tables referencing a parnet tablePeter Brawley10 May
        • Re: Reporting child tables referencing a parnet tableBen Clewett11 May
        • Re: Reporting child tables referencing a parnet tableBen Clewett11 May
        • Re: Reporting child tables referencing a parnet tableBen Clewett11 May
          • Re: Reporting child tables referencing a parnet tablePeter Brawley11 May