| Bug #18483 | Cannot create table with FK constraint | ||
|---|---|---|---|
| Submitted: | 24 Mar 2006 13:57 | Modified: | 1 Jun 2006 12:42 |
| Reporter: | Johan Andersson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any (*) |
| Assigned to: | Tomas Ulin | CPU Architecture: | Any |
[24 Mar 2006 14:03]
Jonas Oreland
Johan, you do know that FK isnt really supported neither in 4.1,5.0 or 5.1 5.0 (and 4.1) probably just silently ignores the FK statement... If you think that the "missing silently ignore" part is a bug...please repoen the bug.
[24 Mar 2006 14:06]
Johan Andersson
If it is ignored on myisam, then it should be ignored on ndb as well..
[27 Mar 2006 15:11]
Valeriy Kravchuk
According to the manual (http://dev.mysql.com/doc/refman/5.1/en/create-table.html): "For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements." it is a bug.
[1 Jun 2006 11:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/7151
[1 Jun 2006 11:02]
Tomas Ulin
fix pushed to 5.1.12
[1 Jun 2006 12:42]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Documented bugfix in 5.1.12 changelog. Closed.
Description: Mysql 5.1: mysql> CREATE TABLE t1 (a VARCHAR(255) NOT NULL, -> CONSTRAINT pk_a PRIMARY KEY (a) )engine=ndb; Query OK, 0 rows affected (0.10 sec) mysql> mysql> CREATE TABLE t2(a VARCHAR(255) NOT NULL, -> b VARCHAR(255) NOT NULL, -> c VARCHAR(255) NOT NULL, -> CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), -> CONSTRAINT fk_a FOREIGN KEY(a) -> REFERENCES t1(a))engine=ndb; ERROR 1215 (HY000): Cannot add foreign key constraint mysql> This works for Innodb and Myisam: mysql> CREATE TABLE t1 (a VARCHAR(255) NOT NULL, -> CONSTRAINT pk_a PRIMARY KEY (a) )engine=myisam; Query OK, 0 rows affected (0.02 sec) mysql> mysql> CREATE TABLE t2(a VARCHAR(255) NOT NULL, -> b VARCHAR(255) NOT NULL, -> c VARCHAR(255) NOT NULL, -> CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), -> CONSTRAINT fk_a FOREIGN KEY(a) -> REFERENCES t1(a))engine=myisam; Query OK, 0 rows affected (0.03 sec) ---------------------------- This works in 5.0 also for NDB!!!!! So something is broken in 5.1 How to repeat: CREATE TABLE t1 (a VARCHAR(255) NOT NULL, CONSTRAINT pk_a PRIMARY KEY (a) )engine=ndb; CREATE TABLE t2(a VARCHAR(255) NOT NULL, b VARCHAR(255) NOT NULL, c VARCHAR(255) NOT NULL, CONSTRAINT pk_b_c_id PRIMARY KEY (b,c), CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES t1(a))engine=ndb; Suggested fix: You know better than me..