Bug #546 fk problems
Submitted: 30 May 2003 7:47 Modified: 30 May 2003 8:00
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.13-nt OS:Microsoft Windows (w2k)
Assigned to: CPU Architecture:Any

[30 May 2003 7:47] [ name withheld ]
Description:
DROP TABLE IF EXISTS a;

CREATE TABLE A (  A_ID BIGINT NOT NULL AUTO_INCREMENT
                , primary key ( a_id )
               ) TYPE = InnoDB;

DROP TABLE IF EXISTS b;

CREATE TABLE b (  B_ID BIGINT NOT NULL AUTO_INCREMENT
                , primary key ( b_id )
               ) TYPE = InnoDB;

DROP TABLE IF EXISTS a_b;

CREATE TABLE a_b (  a_id BIGINT NOT NULL
                  , b_id BIGINT NOT NULL
                  , primary key ( a_id, b_id )
                  , foreign key ( a_id ) REFERENCES a ( a_id ) 
/*                  , foreign key ( b_id ) REFERENCES b ( b_id ) */
                 )  TYPE = InnoDB;

If the pk of a_b is defined ( b_id, a_id ) instead of ( a_id, b_id ) i can define the b-fk, but not the a-fk and vice versa.

How to repeat:
See description.

Suggested fix:
-
[30 May 2003 8:00] Heikki Tuuri
Hi!

http://www.innodb.com/ibman.html#InnoDB_foreign_keys\

"there must be an index where the foreign key and the referenced key are listed as the FIRST columns"

You must define also an index where B_ID is the first column.

Regards,
Heikki
[30 May 2003 11:38] [ name withheld ]
I read over it. Apologies! :-)