MySQL inserting same rows

Question

There is a sql table like that

Indexes like that

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);

At edit's error: That's because you already have some duplicate data in table. You have to remove it before adding constraint. Also, drop the earlier unique index/constraint, it's redundant. — I_am_Batman, May 03 '16 at 20:25

score 2 · Accepted Answer · answered May 03 '16 at 19:34

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.

Bulat · Answer 2 · 2016-05-03T19:44:06.237

1

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

edited May 03 '16 at 19:44

answered May 03 '16 at 19:36

Bulat

6,869
1
29
52

ALTER TABLE users_words ADD CONSTRAINT my_unique UNIQUE(iduser,idword); i tried this code then comes error "#1062 - Duplicate entry '1-1' for key 'my_unique'" – Hayrullah Cansu May 03 '16 at 19:46
In databases usually you are supposed to prepare the data before you can add the constraint. See this - http://stackoverflow.com/questions/8053447/mysql-alter-ignore-table-gives-integrity-constraint-violation – Bulat May 03 '16 at 19:49

MySQL inserting same rows

2 Answers2