List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:May 10 2006 2:56pm
Subject:Re: Reporting child tables referencing a parnet table
View as plain text  
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)

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

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