CODEWITHSUNDEEP
mysqlprimary-key
Jithin
Jithin

Reputation: 59

Mysql Primary key not working?

In my mysql table i have given primary key for

id (int)(auto_incremet)
mobile_number(bigint)
email(varchar)

but i can enter same email and mobile number again. it is not returning an error. I want the email and mobile number as unique in my table. But duplicate values are also allowed even after setting the primary key.

the code i used was 

    create table sample(id int(11) auto_increment,
           mobile bigint(20),email varchar(50),primary key(id,mobile,email));

while describing it is shown as primary key for all three..

where i go wrong.. suggest me a sollution please .. Thank you

Upvotes: 0

Views: 3157

Answers (4)

Uday
Uday

Reputation: 365

Here you are creating a primary key by using the combination of id, mobile and email. So as per the definition of primary key, these three attributes (id, mobile, email) combined used to distinguish between any two rows and id for every row is unique as it is set to auto increment so even if the email and mobile values for two or more rows are same, the combination of id, mobile and email is unique.

In order to get what you want you can do like this

CREATE TABLE sample (
  id INT NOT NULL AUTO_INCREMENT,
  mobile_number BIGINT(20) NULL UNIQUE,
  email VARCHAR(50) NULL UNIQUE,
  PRIMARY KEY(id)
 );

Upvotes: 4

yeputons
yeputons

Reputation: 9248

primary key(id,mobile,email) means that the tuple of these three columns is a primary key. That means that all tuples in the table should be distinct, i.e. you cannot have two rows with same id and mobile and email. However, it's perfectly fine if two rows have same email while having different id.

If you want each column to be unique, you should specify that separately for each column with UNIQUE constraint on a column

Upvotes: 1

lpgad
lpgad

Reputation: 141

 CREATE TABLE sample(id INT(11) AUTO_INCREMENT,
           mobile BIGINT(20),email VARCHAR(50),PRIMARY KEY(id,mobile,email));

The last face of your sql is missing brackets

Upvotes: 0

Robby Cornelissen
Robby Cornelissen

Reputation: 97381

The way you have it, only the combination of id, mobile_number and email is required to be unique.

Use the id as the primary key and set additional unique constraints on the email and mobile_number columns:

CREATE TABLE my_db.sample (
  id INT NOT NULL AUTO_INCREMENT,
  mobile_number BIGINT(20) NULL,
  email VARCHAR(45) NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX mobile_number_UNIQUE (mobile_number ASC),
  UNIQUE INDEX email_UNIQUE (email ASC));

Upvotes: 3

Related Questions