Re: Can't create FOREIGN KEY restraints on InnoDb tables

[Heikki Tuuri]

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