MySQL Bugs: #13395: Can't create table with partial foreign key

Bug #13395	Can't create table with partial foreign key
Submitted:	22 Sep 2005 3:03	Modified:	15 Sep 2006 16:10
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.14	OS:	Windows (WinXP SP2)
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any
Tags:	Object Editors

Description:
Server 4.1.11 on linux

With QB table editor, I'm unable to create a table with a foreign key which references a partial index: the SQL generated for the table creation is incorrect.

How to repeat:
1) Use QB table editor to create table foo with a composite key index on 2 columns.

2) Create table bar with a column as a foreign key referencing foo's first column.

3) The SQL generated for the table creation contains syntax errors:

CREATE TABLE `bar` (
  `id_bar` int(10) unsigned NOT NULL auto_increment COMMENT 'The bar Id',
  `value` varchar(45) NOT NULL default '' COMMENT 'The value',
  `id_foo` int(10) unsigned NOT NULL default '0' COMMENT 'The foo associated in the user language',
  PRIMARY KEY  (`id_bar`),
  CONSTRAINT `bar_1_fk` FOREIGN KEY (`id_foo`, ``) REFERENCES `foo` (`id_foo`, ``)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The bar table with partial references';

Suggested fix:
Support partial foreign key references, as supported by server: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html

Correct syntax should be:
CREATE TABLE `bar` (
  `id_bar` int(10) unsigned NOT NULL auto_increment COMMENT 'The bar Id',
  `value` varchar(45) NOT NULL default '' COMMENT 'The value',
  `id_foo` int(10) unsigned NOT NULL default '0' COMMENT 'The foo associated in the user language',
  PRIMARY KEY  (`id_bar`),
  CONSTRAINT `bar_1_fk` FOREIGN KEY (`id_foo`) REFERENCES `foo` (`id_foo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The bar table with partial references';

Create first table foo with composite index

Attachment: create-table-foo.gif (image/gif, text), 9.49 KiB.

Define table bar with foreign key referencing partial foo's index

Attachment: edit-bar.gif (image/gif, text), 28.97 KiB.

Generated SQL for bar table creation, with syntax errors

Attachment: create-table-bar.gif (image/gif, text), 10.22 KiB.

Error message: key not found

Attachment: key-not-found.gif (image/gif, text), 9.81 KiB.

Empty column lines should not be taken account

Attachment: empty-keys.gif (image/gif, text), 9.68 KiB.

Hi!

mysql> CREATE TABLE `bar` (
    ->   `id_bar` int(10) unsigned NOT NULL auto_increment COMMENT 'The bar Id',
    ->   `value` varchar(45) NOT NULL default '' COMMENT 'The value',
    ->   `id_foo` int(10) unsigned NOT NULL default '0' COMMENT 'The foo associated in
    '> the user language',
    ->   PRIMARY KEY  (`id_bar`),
    ->   CONSTRAINT `bar_1_fk` FOREIGN KEY (`id_foo`, ``) REFERENCES `foo` (`id_foo`,
    -> ``)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The bar table with partial
    '> references';
ERROR 1072 (42000): Key column '' doesn't exist in table

This error is because of an incorrectly formed CREATE TABLE statement.  Please make sure you abide sql standards for constraints and foreign keys.

Hi,

I know the create table DDL statement is incorrect, and that's precisely where lies the problem: this statement was automatically generated by QB when using the table editor. The bug is in the "table editor"; I'm able to write the correct create statement in the command field...

I wasn't sure to be able to explain how that occured, so I attached many screenshots to show the process. But I'm trying now to explain how QB generates this incorrect create table statement:

1) I want to create the bar table, using QB table editor. I define all columns without problem. When I want to create the foreign key, I use the "foreign key" tab (look http://bugs.mysql.com/file.php?id=1876)

2) Though the key on the foo table uses two columns, I want my foreign key to reference only the first column. So I double-click in the "Column|Foreign Colum" list on the "language" column and clear the value.
Now, visually, the table editor displays the table that I want to create (ie. If I create the table from the mysql command line and then open its definition in the table editor, that's the same view).

3) Now, let's press the [Apply changes] button. In this window (http://bugs.mysql.com/file.php?id=1877), you see that the create statement is incorrect.
What happens is that QB does not trim the content of the cells in "foreign key" list and tries to use a `` column.

What should be done:
- QB check in the "foreign key" list that for every "Column" value, there is a non empty "Foreign Column" value; and reverse check.
- The previous check should trim what the user has entered and reject empty cells.
- If both cells on a line are empty, even if the user has edited them, they should not be part of the foreign key definition (else you get ivalid syntax; see http://bugs.mysql.com/file.php?id=1879).

I hope that my explanations are sufficently detailed for you because that is obviously a QB table editor bug. If you need more explanations, please ask.

Hi!

Sorry, I missunderstood the bug.  I will try to reproduce it with what you stated in your previous message.

Again, I apologize for the confusion.

Hi, no trouble explaining again. I know how difficult it is to create a good bug report, particularly when English is not your native language.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Hi,

it's allowed to create an FK which refers only to an index, which is not true in this case (a part of an index is not an index itself). The SQL that you suggested as correct didnt work for me in command line client. (errno: 150 - problems with fk creation). QB is not just designed for such scenarios, and thus gives incorrect syntax...

sorry, I was wrong - for the 1st index column this should work, so this is a bug.

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

I also faced the same problem. 
But when I changed the engine to INNODB. It worked.
You can have multiple columns as foreign key to multiple columns in a referenced table ( which has the columns as primary key ). It fails in the default setting when tables get created as ENGINE=MYISAM.
Just alter the tables as 'alter table <table name> ENGINE=INNODB' and the problem would be solved.