ive got four tables with one primary key in each of them. creating this and updating works. but when i add a foreign key i can only update the database one, a second time it errors with:
Duplicate key name, FK_Buch_6314
Database creation (no foreign key)
drop table if exists Buch;
drop table if exists Autor;
drop table if exists CDDVD;
drop table if exists Format;
create table Buch (Titel longtext, BID int not null auto_increment, primary key (BID)) CHARACTER SET UTF8;
create table Autor (Name longtext, Vorname longtext, AID int not null auto_increment, primary key (AID)) CHARACTER SET UTF8;
create table CDDVD (Titel longtext, Beschreibung longtext, Label longtext, Format int, Interpret longtext, CDDVDID int not null auto_increment, primary key (CDDVDID)) CHARACTER SET UTF8;
create table Format (AudioCD int, DatenCD int, DatenDVD int, FormatID int not null auto_increment, primary key (FormatID)) CHARACTER SET UTF8;
Adding a foreign key
Choose 1:1 relation from menu, first click table Autor, second table Buch
alter table Buch add column AutorAID int;
alter table Buch add index FK_Buch_6134 (AutorAID), add constraint FK_Buch_6134 foreign key (AutorAID) references Autor (AID);
Why the index at all? and why the number after FK_Buch ?
Regarding the problem, please choose InnoDB from MySQL database configuration, only this option will support constraints (InnoDB is required to drop and create the table).
Why the index at all?
Index is required for creating InnoDB foreign key
and why the number after FK_Buch?
The number is auto generated for the constraint name, the name can be manually specified by editing the foreign key name.
mysql> SHOW VARIABLES LIKE ‘have_innodb’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| have_innodb | YES |
±--------------±------+
1 row in set (0.03 sec)
from the logs:
060410 16:59:42 InnoDB: Started; log sequence number 0 44164
060410 16:59:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.0.19-log’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 Gentoo Linux mysql-5.0.19
060411 1:29:49 [Note] /usr/sbin/mysqld: Normal shutdown
I missed an InnoDB entry in the visual paradigm mysql connection dialog, anyway why let the user enable other table engines and use foreign keys?
The programm should warn the user that he has to select InnoDB or not to use foreign keys.
Thank you for the reply. You are right, I just talked to our engineers and they told me that we will show a warning to user. Thank you for the suggestion.
this post is a bit off-topic but I have a little question:
I’m trying the DB-VA for 30 days with the evaluation copy and I’m really impressed. But I don’t understand one thing:
I have a well working MySQL-Database-Connection but every table-column with the datatype longtext or mediumtext is reversed as “integer” with a huge size… reimporting will break up the table… and I’m not able to choose the data-type “mediumtext” in the drop-down box, when I’m editing the column-spec.
Do I have the problem, because I’m using the evaluation copy?
I am new to this tool and in the process of evaluating this. I encountered this warning that InnoDB need to be if you have foreign keys. However, it took a long time for me to find out where to make that setting. Apparently, the Engine options on the Database Configuration screen are not getting displayed sometimes, if you havent selected the first time and created the connection, when you go back it doesnt display the option. Need to play around/clear to make it appear back. Looks like a bug