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