Thanks for the response! Actually, we did notice the on delete null issue, and we even found another. Basically, we have been using PostgreSQL (yikes!), and it just wasn't as 'smart' as MySQL w/ InnoDB. We could actually create foreign keys on tables that didn't even exist yet! But! MySQL to the rescue! It errored out (as you would expect). Thanks for all the help!
-James -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 1:11 AM To: [EMAIL PROTECTED] Subject: Re: Error 150 on 3.23.51 James, ----- Original Message ----- From: ""James Kelty"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, July 13, 2002 12:43 AM Subject: Error 150 on 3.23.51 > Hello, > > We have compiled in InnoDB support for MySQL 3.23.51. We would LOVE to take > advantage of the transactions and foreign key assignments that it provides. > > We are having an issue, with the following SQL, however: > All of the alter table statements are ending with this error: > ERROR 1005: Can't create table './everbase/#sql-955_b.frm' (errno: 150) > > I found references to a bug that was fixed in .51, but I can't find anything > as to what is going on. We think we have the syntax right, but some help > would be appreciated. Thanks! > > -James > P.S. I also included my my.cnf file as well and the bottom. > > create table FailedPaymentContact ( > FailedPaymentContactGenKey int AUTO_INCREMENT PRIMARY KEY, > ClearingHouseCreditCardFKey int NOT NULL, > INDEX > iFailedPaymentContact_ClearingHouseCreditCardFKey(ClearingHouseCreditCardFKe > y), > ContactDate timestamp NOT NULL, > ValidContactTypeFKey int NOT NULL, > INDEX iFailedPaymentContact_ValidContactTypeFKey(ValidContactTypeFKey), > EmployeeFKey varchar(50) NOT NULL, > INDEX iFailedPaymentContact_EmployeeFKey(EmployeeFKey) > ) TYPE = InnoDB; > > > ALTER TABLE FailedPaymentContact ADD CONSTRAINT > FK_FailedPaymentContact_N400038 FOREIGN KEY ( ClearingHouseCreditCardFKey ) > REFERENCES > ClearingHouseCreditCard (ClearingHouseCreditCardGenKey) > ON DELETE SET NULL ON UPDATE CASCADE > ; you have specified ClearingHouseCreditCardFKey int NOT NULL but want it to be set NULL on the delete of the parent row: ON DELETE SET NULL. At least this will produce error 150. InnoDB does not support ON UPDATE CASCADE. Are there ever updates of referenced keys? Usually referenced keys are primary keys, and primary key updates are rare. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
