David, http://www.innodb.com/ibman.html#InnoDB_foreign_keys:
"Starting from 4.0.5 the InnoDB parser is aware of possible option lower_case_table_names you give in my.cnf." Upgrade to 4.0.11! Heikki Innobase Oy sql query ............... Subject: Can't create FOREIGN KEY restraints on InnoDb tables From: David Martin-Roche (ECE) Date: Wed, 26 Feb 2003 11:36:56 +0100 Hi all, I had a problem with mySQL 3.23.55 running on Solaris and on Linux (this problem doesn't occur in NT). When I'm creating the tables in the database, the next error is always provoked: ERROR 1005 at line 54: Can't create table './mideye/profiles.frm' (errno: 150) I'm using InnoDB tables with these lines in my.cnf file: ... [mysqld] set-variable = lower_case_table_names=1 ... # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = innodb_data_file_path = /var/lib/mysql/mideye/InnoDB_Datafile1:10M;/var/lib/mysql/mideye/Inno DB_Datafile2:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/mideye/InnoDB_log innodb_log_arch_dir = /var/lib/mysql/mideye/InnoDB_log ... innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 .... It doesn't happen when lower_case_table_names=0 in my.cnf file. The tables creation fails in the creation of PROFILES table below: CREATE TABLE OAM_OPERATIONS ( ID_OPERATION INTEGER(2) NOT NULL, DESCRIPTION BINARY(50) NOT NULL, URL BINARY(255) NOT NULL, PRIMARY KEY (ID_OPERATION) )TYPE=INNODB; CREATE TABLE PROFILE_DESCRIPTIONS ( ID_PROFILE INTEGER(2) NOT NULL, DESCRIPTION BINARY(50)NOT NULL, PRIMARY KEY (ID_PROFILE) )TYPE=INNODB; CREATE TABLE PROFILES ( ID_PROFILE INTEGER(2) NOT NULL, ID_OPERATION INTEGER(2) NOT NULL, INDEX PROFILE_1 (ID_PROFILE), INDEX PROFILE_2 (ID_OPERATION), CONSTRAINT PROF_CP PRIMARY KEY (ID_PROFILE,ID_OPERATION), CONSTRAINT PROF_PROF_FK FOREIGN KEY (ID_PROFILE) REFERENCES PROFILE_DESCRIPTIONS(ID_PROFILE), CONSTRAINT PROFILES_FK FOREIGN KEY (ID_OPERATION) REFERENCES OAM_OPERATIONS(ID_OPERATION) )TYPE=INNODB; I've checked all the syntax related with foreign keys and it seems to be OK, I've also checked a lot of mails related with this issue without any answer. Any help is welcome. Thanks in advance / David --------------------------------------------------------------------- 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
