SOLVED the problem. Yes it is a different behavior between the two
versions. in 4.0.1-alpha-win the difference between BIGINT and BIGINT
UNSIGNED slipped through. On 4.0.15-win, the foreign key creation
process distinguished the difference and considers the types
different, thus not allowing the foreign key to be created.
ghendrey@telcontar.com (geoff) wrote in message news:<9951531d.0309301852.170f77e4@posting.google. com>...[color=blue]
> 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);[/color]
