CODEWITHSUNDEEP
mysql
getSurreal
getSurreal

Reputation: 377

How to specify index name on unique field in mysql

I'm specifying a field, username, as unique for my application. As a part of the install the user is able to specify a table prefix if they choose. But the unique index that is automatically created does not inherit that table prefix.

    CREATE TABLE IF NOT EXISTS ".$prefix."users (
          id int(11) NOT NULL AUTO_INCREMENT,
          username varchar(50) UNIQUE NOT NULL,
          PRIMARY KEY (id)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

The index that is automatically created is just named username. If there is a second installation to the same database there would be a conflict.

Is there a way to have the index created with the prefix or do I need to delete the index after the table is created and recreate it with the prefix?

Upvotes: 1

Views: 478

Answers (2)

Bohemian
Bohemian

Reputation: 425033

That's not true that index names collide. If you execute this:

create table x1 (x int unique);
create table x2 (x int unique);

there are no errors, therefore no index name collision.

Upvotes: 0

Ramon
Ramon

Reputation: 8424

You can supply an explicit index name like so: 

CREATE TABLE IF NOT EXISTS ".$prefix."users (
      id int(11) NOT NULL AUTO_INCREMENT,
      username varchar(50) NOT NULL,
      PRIMARY KEY (id),
      UNIQUE $unique_index_name (username)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Upvotes: 1

Related Questions