reverse engineering of MySQL ignores unique index clauses

[Former Member]

I'm trying to reverse engineer my mysql DB, and I'm stuck on the unique index productions.

The script I'm using is correct as I'm currently using it in my production environment.

My original script had this syntax:

create table ACL {

     id varchar(255) not null,

     acl_id varchar(255) not null,

     priority integer not null,

     unique (acl_id, priority)

};

This created a column named 'unique' of type '(acl_id, priority)'.  Scratch that.

If I use this alter table syntax, the statement is silently ignored: (No indication on the console tab it was dropped)

    alter table ACL

        add unique index xxxx (acl_id, priority);

The UNIQUE keyword seems to be the troublesome bit here.  This statement is accepted:

    alter table ACL_ENTRY

        add index xxxx (acl_id, priority);

Thanks for any pointers.

-Evan-

[former_member186838]

Hi Evan,

as  far as I know only outer create index statements are supported with the MySQL xdbs.

So you have to use two statments like

create table ACL
(
   id                   varchar(255) not null,
   acl_id               varchar(255) not null,
   priority             integer not null
);

create unique index xxxx on ACL
(
   acl_id,
   priority
);

Regarding the reverse of the unique index it seems to be an issue inside the xdb.

If i look at MYSQL50.XDB under Script\Objects\Index\Create I see

[.O:[create [%UNIQUE% ][%FullText%?fulltext ]index %INDEX% on [%QUALIFIER%]%TABLE%

(

   %CIDXLIST%

)][alter table [%QUALIFIER%]%TABLE% add index %INDEX%

(

   %CIDXLIST%

)]]

seems the [%UNIQUE% ] for alter is not supportet as well.

If I modify this to

....

)][alter table [%QUALIFIER%]%TABLE% add [%UNIQUE% ] index %INDEX%

....

I was able to reverse the unique index.

best regards

Stefan