Reputation: 59
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
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
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
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
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