Here is the error from the show innodb status: 070523 15:39:39 Error in foreign key constraint of table mysql/#sql-d70_22:
FOREIGN KEY (tab_id, class) REFERENCES system_tabs(tab_id, class): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. I don't understand this since the primary key for system_tabs is tab_id, class and clinic_consent_form has the index fk_idx on tab_id, class. What is mysql complaining about? Chris On 5/23/07, Baron Schwartz <[EMAIL PROTECTED]> wrote:
Hi Chris, Chris Hoover wrote: > I need some help. I am under presure from management to migrate and > existing Sybase ASA database to Mysql for a proof of concept. > > I have done most of the work, but am having an issue creating a foreign > key. I keep getting the "Can't create table './mysqk/#sql-d70_1f.frm' > (errno: 150)" error. I have looked in the documentation, and everything > seems to be fine. Please advise on what is wrong. > > Thanks, > > Chris > > Here is the offending statement: > > ALTER TABLE clinic_consent_form > ADD CONSTRAINT fk_clinic_c_ref_782_system_t > FOREIGN KEY (tab_id, class) REFERENCES system_tabs(tab_id, class); > > > Here are the two tables involved: > CREATE TABLE `system_tabs` ( > `tab_id` bigint(20) unsigned NOT NULL auto_increment, > `object_name` varchar(32) NOT NULL, > `description` varchar(64) NOT NULL, > `class` int(11) NOT NULL, > `audit_flag` smallint(6) NOT NULL default '0', > `active_ind` char(1) NOT NULL default 'Y', > PRIMARY KEY (`tab_id`,`class`), > UNIQUE KEY `tab_id` (`tab_id`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > CREATE TABLE `clinic_consent_form` ( > `clinic_id` bigint(20) NOT NULL, > `tab_id` bigint(20) NOT NULL, > `class` int(11) NOT NULL, > PRIMARY KEY (`clinic_id`,`tab_id`,`class`), > KEY `fk_idx` (`tab_id`,`class`), > CONSTRAINT `fk_clinic_c_ref_473_clinic` FOREIGN KEY (`clinic_id`) > REFERENCES `clinic` (`clinic_id`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > Try running SHOW INNODB STATUS and examining the LAST FOREIGN KEY ERROR section. InnoDB is very picky about foreign keys. Types must match exactly, indexes have to be created, etc. I don't see anything wrong with the table definitions here, but there may be some data already in the tables that would violate a foreign key. Cheers Baron -- Baron Schwartz http://www.xaprb.com/
