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

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