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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to