Devart Forums

Posted: **Tue 08 Dec 2009 12:02**

Hello Support Team,

I have a table to define a n:m relation and it is defined in my project file as following:

CREATE TABLE auth_right_group (
  GRPID INT(11) NOT NULL COMMENT 'group id',
  RIGHTID INT(11) NOT NULL UNIQUE COMMENT 'right ID',
  PRIMARY KEY (GRPID, RIGHTID),
  INDEX idx_auth_right_group_grpid USING BTREE (GRPID),
  INDEX idx_auth_right_group_rightid USING BTREE (RIGHTID),
  CONSTRAINT fk_auth_right_group_auth_group FOREIGN KEY (GRPID)
    REFERENCES auth_group(GRPID) ON DELETE CASCADE,
  CONSTRAINT fk_auth_right_group_auth_right FOREIGN KEY (RIGHTID)
    REFERENCES auth_right(RIGHTID) ON DELETE CASCADE
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

when synchoniszing with db server, dbForge Studio generates this DDL for the table above:

Code: Select all

CREATE TABLE auth_right_group (
  GRPID INT(11) NOT NULL COMMENT 'group id',
  RIGHTID INT(11) NOT NULL COMMENT 'right ID',
  PRIMARY KEY (GRPID, RIGHTID),
  INDEX idx_auth_right_group_grpid USING BTREE (GRPID),
  INDEX idx_auth_right_group_rightid USING BTREE (RIGHTID),
  CONSTRAINT fk_auth_right_group_auth_group FOREIGN KEY (GRPID)
    REFERENCES auth_group(GRPID) ON DELETE CASCADE,
  CONSTRAINT fk_auth_right_group_auth_right FOREIGN KEY (RIGHTID)
    REFERENCES auth_right(RIGHTID) ON DELETE CASCADE,
  UNIQUE INDEX RIGHTID (RIGHTID)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci

the verification ends in state "to update", because it adds the

Code: Select all

UNIQUE INDEX RIGHTID (RIGHTID)

to the DDL statement, but this is absolutly incorrect for this table and is not defined in our project file.

Normally we have data like this in the such kind of table:

Code: Select all

So why does dbForge Studio generate this index?

Executing the synchronisation script will also fail with "duplicate key", because of the data.

Posted: **Tue 08 Dec 2009 13:14**

additional info:

when building the project the DDL for the table is correct without the

Code: Select all

UNIQUE INDEX RIGHTID (RIGHTID)

Posted: **Tue 08 Dec 2009 15:06**

MarkBrock wrote:additional info:

when building the project the DDL for the table is correct without the
Code: Select all
UNIQUE INDEX RIGHTID (RIGHTID)

UNIQUE in CREATE TABLE clause is a synonym for creating a unique key for this column, i.e.,

CREATE TABLE
...
RIGHTID INT(11) NOT NULL UNIQUE COMMENT 'right ID' –creation of a column with a unique key.

is the same as

CREATE TABLE
...
RIGHTID INT(11) NOT NULL COMMENT 'right ID', -- a column creation
...
UNIQUE INDEX RIGHTID (RIGHTID) – creation of a unique key for this column

>So why does dbForge Studio generate this index?

dbForge Studio does this, because this index exists in a table, no matter where it is added – either in the beginning or in the end of CREATE TABLE clause.

We could not reproduce this problem, a project is built successfully. If possible, please send us your script that causes an error while building a project. Send the script to http://www.devart.com/company/contact.html

Posted: **Tue 08 Dec 2009 15:16**

Thx very much!

I haven't seen the definition in the column definition, don't know how it gets there.
When I removed this column definition it works correct now.

Devart Forums

DB Comparison/synchronization issue 2 - Object DDL creation

DB Comparison/synchronization issue 2 - Object DDL creation