Updating mysql database doesn't work

Hi,

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

  1. 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;

  1. 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 ?

Julius

Hi user,

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.

Best Regards,
Jick

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

looks like innobd is alredy in use?

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.

Thx for help.

Julius

Hi Julius,

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.

Fnially, may I confirm if the problem is solved?

Best Regards,
Jick

Hi Julius,

I would like to inform you that the warning will be available in VP Suite 2.2 Service Pack2.

Best Regards,
Jick

Hello,

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?

Thanks for answers…

Hi,

For the last question, please refer to the link below for the answer:
http://forums.visual-paradigm.com/posts/list/1377.html

Best Regards,
Jick

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