Re: How to get the name of the last failed FK constraint
[EMAIL PROTECTED] wrote: Hi, Status; gives your own session id. To be combined to show innodb status (ksh or perl). Mathias I'm not sure I understand. Are you saying that SHOW INNODB STATUS shows only the information of the current session or that I can supply a session id to the call? -- Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the name of the last failed FK constraint
[EMAIL PROTECTED] wrote: Frank Schröder [EMAIL PROTECTED] wrote on 05/31/2005 03:18:11 AM: Hello, I have an InnoDB table running on MySQL 4.1.11 with multiple FK constraints. I'm accessing it via JDBC from Java. When an FK constraint fails with error 1216 I need to know which of the constraints failed. SHOW INNODB STATUS returns the following output ... CONSTRAINT `u_registration_ibfk_1` FOREIGN KEY (`DEVICE_ID`) REFERENCES `u_device` (`DEVICE_ID`) ... Is there a way of getting to the name of the last failed FK constraint without using SHOW INNODB STATUS? What I need is the 'u_registration_ibfk_1' from the above example. Any help is highly appreciated -- Frank Have you looked at the results of SHOW INNODB STATUS; ? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yes, as you can see from my original post I'm actually trying to figure out how to do this *without* SHOW INNODB STATUS as this reports the last FK failure for the entire engine and not just my session - at least that's how I interpret the documentation. The thing that's really a headscratcher for me is why its possible for me to set a name for a constraint if it isn't displayed in an error and I can't get to it. It's useless. I have a hard time believing that so I figure that I just haven't figured out how to get to it. I just didn't think that it was so hard. -- Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get the name of the last failed FK constraint
Hello, I have an InnoDB table running on MySQL 4.1.11 with multiple FK constraints. I'm accessing it via JDBC from Java. When an FK constraint fails with error 1216 I need to know which of the constraints failed. SHOW INNODB STATUS returns the following output ... CONSTRAINT `u_registration_ibfk_1` FOREIGN KEY (`DEVICE_ID`) REFERENCES `u_device` (`DEVICE_ID`) ... Is there a way of getting to the name of the last failed FK constraint without using SHOW INNODB STATUS? What I need is the 'u_registration_ibfk_1' from the above example. Any help is highly appreciated -- Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys - No action - Errors
My understanding is that RESTRICT and NO ACTION share the same behavior. http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html * |NO ACTION|: In |ANSI SQL-92| standard, |NO ACTION| means /no action/ in the sense that an attempt to delete or update a primary key value will not be allowed to proceed if there is a related foreign key value in the referenced table (Gruber, Mastering SQL, 2000:181). Starting from 4.0.18 |InnoDB| rejects the delete or update operation for the parent table. * |RESTRICT|: Rejects the delete or update operation for the parent table. |NO ACTION| and |RESTRICT| are the same as omitting the |ON DELETE| or |ON UPDATE| clause. (Some database systems have deferred checks, and |NO ACTION| is a deferred check. In MySQL, foreign key constraints are checked immediately, so |NO ACTION| and |RESTRICT| are the same.) This works only on InnoDB tables as - AFAIK - the InnoDB engine the only engine is which implements FK constraints. on 05/24/2005 07:46 AM Sven Åke Johansson said the following: I have some problem with Foreign Key settings. I use MySQL 4.12, MySQL Query Browser 4.16 and Windows XP. Restrict and Cascade is Ok but when I try to set No action it wont work. Sometimes there is no error message and it seams that the change is saved. But when I check there is no changes. When an error message shows its nr 1005. What is the conditions to set No action. Ok In the manual it says only that No action is taken in the child table when rows are deleted from the parent or values in the referenced columns in the parent table are updated. I read the articles on MySQL , a lot of books and the manual but I cant get any answer. Thanks for any answer wich will solve my problem. Sven Åke Johansson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]