DDL and indexes on export

DBVis 7.0.6, OSX 10.6.3, mySQL 5.1.46. A basic table created with the DBVis "Create Table" pane (notice that the primary key isn't the auto_increment column): CREATE TABLE t2 ( myprimary bigint DEFAULT '0' NOT NULL, autoinc bigint NOT NULL AUTO_INCREMENT, PRIMARY KEY (myprimary), CONSTRAINT ix1 UNIQUE (autoinc) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 But when I use the "Export Table..." feature (or Export Database), choosing the "Generate CREATE statements" and "Include Table Indexes" options, the output is this: CREATE TABLE t2 ( myprimary bigint DEFAULT '0' NOT NULL, autoinc bigint NOT NULL AUTO_INCREMENT, PRIMARY KEY (myprimary) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE UNIQUE INDEX ix1 ON t2 (autoinc); The "ix1" index on the "autoinc" column has been handled as a separate statement, instead of as part of the "CREATE TABLE" command, and only the PRIMARY KEY clause (for "myprimary") is put in the CREATE TABLE. However, since "autoinc" in an auto_increment column, it needs to be indexed, and since it isn't as part of the CREATE TABLE command mySQL fails with the error "Incorrect table definition; there can be only one auto column and it must be defined as a key". Presumably in most cases where there is an auto_increment field it is also the primary and this isn't a problem. But in the example shown, it is possible to export out DDL for a table and not be able to run it for recreation, without a manual fixup. I assume there are advantages to generating the indexes as separate SQL statements? If so you might want to put in a check for non-primary auto_increment columns and at a minimum generate a warning in this situation.

Hi, Can you please post (or email) some screenshots that illustrate the problem? Best Regards, Hans

Hi I have try export data from table in SQL output format. Data are export, but primary keys don't export. But primary keys are showing in Primary Key tab of Object View of selected table. Why? Sorry. I understood. I export data from VIEW. :) Edited by: bunak on 04.12.2011 21:33

Hi David, There does not seem to be any distinction between key "constraints" (declared as part of CREATE TABLE) and indexes (created by separate CREATE INDEX statements) in MySQL. SHOW CREATE TABLE shows both as part of the CREATE TABLE statement. The problem you reported will be fixed in the next maintenance release by including all key declarations in the CREATE TABLE statement. Best Regards, Hans

Hi David, The reason for this is that it is hard to tell from the MySQL metadata if an index on a table is a "key constraint" created as part of the table or if it's a separate index. I will have another look at this and see if I can find that distinction somewhere in there. Best Regards, Hans

DId a quick check of some other exports. Navicat Lite, phpMyAdmin and SequelPro (a Mac GUI) all seem to export the indexes as part of the CREATE TABLE command. RazorSQL does it the DbVis way, with separate CREATE INDEX statements. But they oddly seem to drop the "AUTO_INCREMENT" out of the DDL, so they probably shouldn't be considered a role model... :)