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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
