CODEWITHSUNDEEP
mysql
Skyyy
Skyyy

Reputation: 1569

Duplicate data is inserted with unique index

I have a table called users with 4 unique columns and when I insert data in email it doesn't give me any error and inserts the data even when when the same value already exists in that column.

Here is my database structure:

$user = "CREATE TABLE IF NOT EXISTS users(
id INT UNSIGNED AUTO_INCREMENT,
fb_id BIGINT UNSIGNED NULL,
google_id BIGINT UNSIGNED NULL,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NULL,
email VARCHAR(320) NOT NULL,
username VARCHAR(20) NULL,
password VARCHAR(255) NULL,
access_token TEXT NULL,
type ENUM('facebook','google','site') NOT NULL,
gender ENUM('m','f','o') NULL,
reg_date DATE NOT NULL,
token_expire DATETIME NULL,
PRIMARY KEY(id),
UNIQUE(email,username,fb_id,google_id)
)";

But, when I create my table with following structure:

$user = "CREATE TABLE IF NOT EXISTS users(
id INT UNSIGNED AUTO_INCREMENT,
fb_id BIGINT UNSIGNED NULL UNIQUE,
google_id BIGINT UNSIGNED NULL UNIQUE,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NULL,
email VARCHAR(320) NOT NULL UNIQUE,
username VARCHAR(20) NULL UNIQUE,
password VARCHAR(255) NULL,
access_token TEXT NULL,
type ENUM('facebook','google','site') NOT NULL,
gender ENUM('m','f','o') NULL,
reg_date DATE NOT NULL,
token_expire DATETIME NULL,
PRIMARY KEY(id)
)";

It gives me an error when there is a duplicate entry.

Creating table with any of those methods doesn't give any error. After creating the tables I have verified with phpmyadmin that all those columns have unique index in both methods.

Upvotes: 1

Views: 119

Answers (1)

Drew
Drew

Reputation: 24959

Akash, in the 1st create table, the composite (combination) is unique. If you want them individually to be unique, separate them into ... separate UNIQUE key statements, like in the 2nd.

Let's say the bottom of your first table read this

PRIMARY KEY(id),
UNIQUE KEY(email,username,fb_id,google_id)

Then there is nothing wrong with these two rows existing in the composite index:

'[email protected]','Akash',101,102

and

'[email protected]','Akash2',101,102

Upvotes: 3

Related Questions