Bug #546 fk problems

• Submitted: 30 May 2003 7:47
• Modified: 30 May 2003 8:00
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.13-nt
• OS: Windows (w2k)
• 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! :-)