CODEWITHSUNDEEP
mysqlsqlsql-insertunique-index
Hayrullah Cansu
Hayrullah Cansu

Reputation: 262

MySQL inserting same rows

There is a sql table like that enter image description here

Indexes like that enter image description here

INSERT INTO users_words(iduser,idword,status_of_word) VALUES(4,6,2);
INSERT INTO users_words(iduser,idword,status_of_word) VALUES(4,6,1);
INSERT INTO users_words(iduser,idword,status_of_word) VALUES(4,6,0);
INSERT INTO users_words(iduser,idword,status_of_word) VALUES(5,6,0);
INSERT INTO users_words(iduser,idword,status_of_word) VALUES(8,7,1);
INSERT INTO users_words(iduser,idword,status_of_word) VALUES(8,9,2);

I want to a unique index is together with iduser,idword. How to avoid inserting query with same values together index columns? For example: Bloking row inserting like 13 and 14 ( idusers_words ) rows

SELECT * FROM users_words;

> idusers_words  |  iduser | idword | status_of_word |
         12             4       6            2
         13             4       6            1
         14             4       6            0
         15             5       6            0
         16             8       7            1
         17             8       9            2

EDIT:

I tried this code. But comes that error "#1062 - Duplicate entry '1-1' for key 'my_unique'"

ALTER TABLE users_words
ADD CONSTRAINT my_unique UNIQUE(iduser,idword);

Upvotes: 1

Views: 64

Answers (2)

Bulat
Bulat

Reputation: 6969

You can put a constraint on a set of fields like this:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

Source: http://www.w3schools.com/sql/sql_unique.asp

Upvotes: 1

I_am_Batman
I_am_Batman

Reputation: 915

Your unique index should only be on iduser and idword columns for your requirement.

You have index on idusers_words,iduser and idword. This means combination of all the three columns should be unique.

Since idusers_words is autoincrement, this criteria would always be met,even though idusers and idword are duplicates, and records will be inserted.

Upvotes: 2

Related Questions