| Bug #23338 | Adding FK Alter table statement missing commas | ||
|---|---|---|---|
| Submitted: | 16 Oct 2006 16:01 | Modified: | 19 Oct 2006 13:18 |
| Reporter: | Trent Johnsey | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Administrator | Severity: | S3 (Non-critical) |
| Version: | 1.2.4 rc | OS: | Windows (XP Pro SP2) |
| Assigned to: | Vladimir Kolesnikov | CPU Architecture: | Any |
[16 Oct 2006 16:13]
Trent Johnsey
After more attempts, the problem is specifically the last two fields of the alter table statement. See example below. The commas are there for multiple fields, but not for last two fields. Thus, this issue was encountered with only two fields.
ALTER TABLE `dhpcsdata`.`dtlreceiptdtl_serial` ADD CONSTRAINT `FK_dtlreceiptdtl_RecpSerial` FOREIGN KEY `FK_dtlreceiptdtl_RecpSerial` (`fldSerialPONUM`, `fldSerialORDNUM`, `fldSerialRECNUM`, `fldSerialLINE`, `fldSerialSEQ``fldSerialRECSEQ`)
REFERENCES `dtlreceiptdtl` (`PONUM`, `ORDNUM`, `RECNUM`, `LINE`, `SEQ``RECSEQ`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
[17 Oct 2006 7:17]
Sveta Smirnova
Thank you for the report.
Verified as described.
Create tables as below and then try to add to table `product_order` foreign key (`product_category`,`product_id`) references `product` (`category`,`id`)
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
INDEX (customer_id)) ENGINE=INNODB;
[19 Oct 2006 13:18]
Vladimir Kolesnikov
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
Description: I tried on several occasions to add foreign keys to a table via the Administrator. If the FK setup had more than one field to be linked via a FK, then the subsequently created SQL would not put a comma between the fields. Thus, the alter statement would fail. I could easily correct the statement using a text editor and then pasting the code into the command prompt. The SQL below was created via the Administrator. you can see the comma missing between PONUM and ORDNUM. ALTER TABLE `dhpcsdata`.`dtlchangedtl` ADD CONSTRAINT `FK_dtlPOHdrChangedtl` FOREIGN KEY `FK_dtlPOHdrChangedtl` (`PONUM``ORDNUM`) REFERENCES `dtlpohdr` (`PONUM``ORDNUM`) ON DELETE CASCADE ON UPDATE RESTRICT; How to repeat: Create a foreign key between two tables using more than one record for the reference columns. Suggested fix: make sure the created SQL has a comma between the fields.