The table creation script(at the end of this post) works fine on
4.0.1-alpha-win, but the foreign key constraints fail on 4.0.15-win. I
am starting the server with the same command for both versions:
mysqld-max-nt --console --transaction-isolation=SERIALIZABLE
In 4.0.15-win I can extract the following error after I run the table
creation script:
[test01] ERROR 1005: Can't create table '.\ibdata\#sql-a14_3.frm'
(errno: 150)
Also, the InnoDB status in 4.0.150-win shows:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030930 19:38:49 Error in foreign key constraint of table
ibdata/#sql-a14_3:
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:
FOREIGN KEY (contactInfo_fk) REFERENCES contact(pk) ON DELETE SET NULL
See http://www.innodb.com/ibman.html for correct foreign key
definition.
But the above is wrong. I DO have an index defined on the foreign key,
it IS the first column in the index, and their IS NO type mismatch.
Since this works on 4.0.1, I have to think this is a bug. I need a
working version of the server that supports transaction savepoints.
Any help appreciated!!
# Connection: test01
# Host: localhost
# Saved: 2003-09-29 18:16:42
#
CREATE TABLE orderjoin (
owner BIGINT,owned BIGINT
) TYPE=InnoDB;
CREATE TABLE orders (
pk BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, last_modified
TIMESTAMP,date DATETIME, PRIMARY KEY (pk)
) TYPE=InnoDB;
CREATE TABLE lineitem (
pk BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, last_modified
TIMESTAMP,orderFk BIGINT,description VARCHAR(128),price DOUBLE,
PRIMARY KEY (pk)
) TYPE=InnoDB;
CREATE TABLE alias (
customer_fk BIGINT,alias_str VARCHAR(128)
) TYPE=InnoDB;
CREATE TABLE address (
address_pk BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
last_modified TIMESTAMP,address2 VARCHAR(128),zip
VARCHAR(128),address1 VARCHAR(128),discriminator VARCHAR(128), PRIMARY
KEY (address_pk)
) TYPE=InnoDB;
CREATE TABLE contact (
pk BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, last_modified
TIMESTAMP,email VARCHAR(128),phone VARCHAR(128), PRIMARY KEY (pk)
) TYPE=InnoDB;
CREATE TABLE discount (
pk BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, last_modified
TIMESTAMP,percent FLOAT,discount_plan_name VARCHAR(128), PRIMARY KEY
(pk)
) TYPE=InnoDB;
CREATE TABLE customer (
pk BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, last_modified
TIMESTAMP,address2 VARCHAR(128),lname VARCHAR(128),address1
VARCHAR(128),discount_fk BIGINT,gender VARCHAR(128),fname
VARCHAR(128),discriminator VARCHAR(128),zip
VARCHAR(128),contactInfo_fk BIGINT, PRIMARY KEY (pk)
) TYPE=InnoDB;
ALTER TABLE orderjoin ADD INDEX (owner);
ALTER TABLE orderjoin ADD FOREIGN KEY (owner) REFERENCES customer(pk)
ON DELETE SET NULL;
ALTER TABLE orderjoin ADD INDEX (owned);
ALTER TABLE orderjoin ADD FOREIGN KEY (owned) REFERENCES orders(pk) ON
DELETE SET NULL;
ALTER TABLE lineitem ADD INDEX (orderFk);
ALTER TABLE lineitem ADD FOREIGN KEY (orderFk) REFERENCES orders(pk)
ON DELETE SET NULL;
ALTER TABLE alias ADD INDEX (customer_fk);
ALTER TABLE alias ADD FOREIGN KEY (customer_fk) REFERENCES
customer(pk) ON DELETE SET NULL;
ALTER TABLE discount ADD UNIQUE uindex (discount_plan_name);
ALTER TABLE customer ADD INDEX (discount_fk);
ALTER TABLE customer ADD FOREIGN KEY (discount_fk) REFERENCES
discount(pk) ON DELETE SET NULL;
ALTER TABLE customer ADD INDEX (contactInfo_fk);
ALTER TABLE customer ADD FOREIGN KEY (contactInfo_fk) REFERENCES
contact(pk) ON DELETE SET NULL;
ALTER TABLE customer ADD UNIQUE uindex (lname);
