CODEWITHSUNDEEP
mysql
Som
Som

Reputation: 970

MySql is not accepting Unique constraint while having Auto Increment for another column

I have the following query:

CREATE TABLE UserDetails (
    sno INT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    location VARCHAR(20) NOT NULL,
    country VARCHAR(20) NOT NULL,
    UNIQUE KEY(username)
);

It is giving the following error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

I want to set sno to auto increment and username to UNIQUE AND NOT NULL.

What am I doing wrong?

Upvotes: 0

Views: 190

Answers (2)

Marc B
Marc B

Reputation: 360602

sno must be defined as a primary key (e.g. unique key) for auto_increment to work:

CREATE TABLE UserDetails (
    sno INT unsigned auto_increment primary key,
    etc...
)

Upvotes: 0

rid
rid

Reputation: 63442

sno needs to be a PRIMARY KEY if you want it to be auto incremented. Replace:

sno INT UNSIGNED AUTO_INCREMENT,

with

sno INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

Upvotes: 2

Related Questions