Package: mysql-server Version: 4.0.23_Debian-7-log
I have created several tables and tried to link them using foreign keys. I used mysql-admin. However it did not work. I asked on irc and several other people encountered the same problem. We believe that it's either a bug in mysql-server or mysql-admin.
We were able to reproduce the problem on mysql 4.0.23_Debian-7-log and 4.1.11 (tests were done by two completely seperate persons who met on irc).
I created two InnoDB-tables, without problems, and then tried to create a foreign key. I used mysql-admin to do this, but using the same sql-commands on any shell produces the same errors....
**** BEGIN SQL ****
# This table contains a list of computers. No problem here.
CREATE TABLE `Computer` ( `Computer-Name` varchar(50) NOT NULL default '', PRIMARY KEY (`Computer-Name`), KEY `Computer-Name` (`Computer-Name`) ) TYPE=InnoDB
# This table contains a list of sound-cards which are built into computers, so the Computer-column should refer to the above table.
Creating the table and setting up the foreign key worked once. However after that I was not able to change the foreign-key settings anymore (error 1005).
CREATE TABLE `Soundkarten` (
`Soundkarten-NR` int(11) NOT NULL auto_increment,
`Soundkarten-Name` varchar(50) default NULL,
`Line-In-Anzahl` int(11) default '0',
`Line-In-Type` varchar(50) default NULL,
`Mic-In-Anzahl` int(11) default '0',
`Mic-In-Type` varchar(50) default NULL,
`Speaker-Out-Anzahl` int(11) default '0',
`Speaker-Out-Type` varchar(50) default NULL,
`Sonstiges-Anzahl/Type` varchar(100) default NULL,
`Joystick` varchar(5) default NULL,
`Sonstiges` varchar(200) default NULL,
`Computer` varchar(50) NOT NULL default '',
PRIMARY KEY (`Soundkarten-NR`),
KEY `Computer` (`Computer`),
CONSTRAINT `new_fk_constraint` FOREIGN KEY (`Computer`) REFERENCES `Computer` (`Computer-Name`)
) TYPE=InnoDB
# This is a list of processors, same foreign key here, however here I was never able to introduce the foreign key.
CREATE TABLE `Prozessoren` (
`Prozessor-Nr` int(11) NOT NULL auto_increment,
`Prozessor-Bezeichnung` varchar(50) default NULL,
`Core` int(11) default '0',
`FSB` int(11) default '0',
`Computer` varchar(50) NOT NULL default '',
PRIMARY KEY (`Prozessor-Nr`),
KEY `{1CF61A17-EB07-4F60-A79B-7E29C1609801}` (`Computer`),
KEY `Computer` (`Computer`)
) TYPE=InnoDB# This is the query I am using to try to configure the foreign key (Created by MySQL-Administration Tool mysql-admin):
ALTER TABLE `privat`.`Prozessoren` MODIFY COLUMN `Prozessor-Nr` INTEGER NOT NULL AUTO_INCREMENT,
MODIFY COLUMN `Prozessor-Bezeichnung` VARCHAR(50) ,
MODIFY COLUMN `Core` INTEGER DEFAULT 0,
MODIFY COLUMN `FSB` INTEGER DEFAULT 0,
MODIFY COLUMN `Computer` VARCHAR(50) NOT NULL,
ADD CONSTRAINT `new_fk_constraint` FOREIGN KEY `new_fk_constraint` (`Computer`)
REFERENCES `Computer` (`Computer-Name`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
**** END SQL *****
This last query was created by mysql-admin. Several people checked it and confirmed that it should work in their opinion, however it does not work on any of the tested systems.
We figured out that if we leave out the CONSTRAINT-part and only the following it works:
"(....)ADD FOREIGN KEY `new_fk_constraint` (`Computer`)
REFERENCES `Computer` (`Computer-Name`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;"
We are not absolutely sure where the bug is...
If the query is correct but doesn't work it's a bug in mysql-server.
If the query is wrong and mysql-admin tries to use it, it's a bug in mysql-admin.
Thanks alot!
Roland Ulbricht
-- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
