Error redefining primary key in mysql

Question

I'm just beginning with mysql. I'm trying to design a table which has columns containing an id which is auto-incremented, a unique username (userid), and a password.

Initially I thought the id should be the primary key so that I could easily update the data referring to the number, as is done in some forum softwares.

So I created the table with:

CREATE TABLE IF NOT EXISTS auth (
    -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> userid VARCHAR(30) NOT NULL,
    -> password VARCHAR(30) NOT NULL,
    -> role VARCHAR(20) NOT NULL,
    -> email VARCHAR(50)
    -> );

However I found that if I did a replace command, it would duplicate rows.

mysql> SELECT * FROM `auth`;
+----+--------+-----------------+---------------+-------+
| id | userid | password        | role          | email |
+----+--------+-----------------+---------------+-------+
|  1 | jody | pw | administrator | NULL  |
+----+--------+-----------------+---------------+-------+
1 row in set (0.00 sec)

mysql> REPLACE INTO `auth`  SET `userid` = "jody", `password` = "pw", `role` = "administrator";
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `auth`;
+----+--------+-----------------+---------------+-------+
| id | userid | password        | role          | email |
+----+--------+-----------------+---------------+-------+
|  1 | jody | pw | administrator | NULL  |
|  2 | jody | pw | administrator | NULL  |
+----+--------+-----------------+---------------+-------+
2 rows in set (0.00 sec)

I then tried to change the primary key to the username, but ran into this:

mysql> alter table `auth` drop primary key, add primary key (`userid`);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

So it seems something's very wrong with my understanding of primary keys. What's the problem here?

see [there-can-be-only-one-auto-column](http://stackoverflow.com/questions/8645889/there-can-be-only-one-auto-column) — amdixon, Sep 20 '15 at 11:22
`ALTER TABLE auth ADD UNIQUE uid (userid)` after deleting the duplicate rows. — hjpotter92, Sep 20 '15 at 11:23

score 1 · Answer 1 · edited Sep 20 '15 at 11:33

primary key should be unique and not null (by definition), and your userid in not unique

To make userid unique from the Create Table do this

CREATE TABLE IF NOT EXISTS auth
(   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    userid VARCHAR(30) NOT NULL,
    password VARCHAR(30) NOT NULL,
    role VARCHAR(20) NOT NULL,
    email VARCHAR(50),
    unique key(userid)
);

or as @hjpotter92 said

ALTER TABLE auth ADD UNIQUE uid (userid)

to do it after the fact (and the resulting schema can be seen through show create table auth)

Remember that there can be only one primary key per table, and if a column is auto_inc, it must be the primary key.

Musa Haidari · Accepted Answer · 2015-09-20T12:01:14.220

This error is happening since you already have one primary key that is id. It is primary key, since it is auto_increment.

Now if you want your username to be primary key, either:

You need to drop id totally: alter table auth drop id;
Or alter it to be a simple int column, and not auto_increment: alter table auth modify id int. For auto increment workaround you can create a before insert trigger.

Update: Do not try to keep id as primary key and adding unique to username, as this will have side effect in referenced tables, as replace command will change the value of auto_incremented id

After you have done any of the above, then:

Make sure your table does not have username duplicates
Issue this: alter table auth username varchar(225) primary key;

Note: Please make sure you do not make username unique while having id as your primary key, since replace command will drop the old record before adding the new one if indexed values are the same, and doing so, the auto_incremented id will again be incremented and this will effect the referenced tables' values.

@Droidzone Trigger as the name suggests, is an action that depends on another action to happen (or another action to trigger it). It can happen before or after that action. Here we need to generate a value for insert, so the trigger type is "before insert". In the definition, you read the prev. value for ID and add by one and assign to the column value. https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html — Musa Haidari, Sep 21 '15 at 04:20

Error redefining primary key in mysql

2 Answers2