How to don´t repeat values stored in database

Question

I´m creating a database addrees and I want to know what I need to set in Mysql to don´t store repeat values?

Like

Addrees 1 ("teste",1,new york,eua);

Addrees 2 ("teste",1,new york,eua);

If this happen my database will not store.

So what I need to do?

Add a unique key on the column that need to be unique – juergen d Oct 20 '13 at 17:23 — juergen d, Oct 20 '13 at 17:23

score 1 · Answer 1 · answered Oct 20 '13 at 17:23

1

You need to call these fields a UNIQUE_KEY

answered Oct 20 '13 at 17:23

La-comadreja

5,627
11
36
64

score 1 · Accepted Answer · edited May 23 '17 at 11:50

To alter an already existing table, run this MySQL command:

alter table yourtablename add unique index(firstcolumn, secondcolumn, thirdcolumn, fourthcolumn);

That'll add the unique constraint to the specified columns. Here's how to specify such a constraint in the CREATE TABLE.

CREATE TABLE buyers ( 
 buyer_id INT UNSIGNED NOT NULL, 
 first_name CHAR(19) NOT NULL, 
 last_name CHAR(19) NOT NULL, 
 age SMALLINT NOT NULL, 
 post_code SMALLINT NOT NULL, 
 UNIQUE idx_flname_age (first_name,last_name,age)
);

The primary key constraint will do this too, as mentioned by @Ajeesh

EDIT: As per the suggestion in the comment, if you want to avoid errors generated by this unique constraint, you have three good options:

INSERT IGNORE

and

INSERT...ON DUPLICATE KEY UPDATE

and

REPLACE

INSERT IGNORE will not do anything if the insert violates the unique constraint, except log a harmless warning. The table will be left as is, and no error would be reported. This may be desireable in some cases.

More commonly is the second option, ON DUPLICATE KEY UPDATE, which says "Well, if the key already exists, then update that key's row like this instead."

And lastly is REPLACE, which will, if the key already exists, delete the row, then do an INSERT as normal. If the key did not exist previously, it will simply act as an INSERT.

This stack overflow answer has some examples.

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

It would be great to mention special procedures required to avoid errors during insertion/updating this table. http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html — Basilevs, Oct 20 '13 at 17:43

score 0 · Answer 3 · answered Oct 20 '13 at 17:29

To make a column to be distinct you need to have Primary Key constraint/Unique Key. Primary key is used for relating one table with another and it's values should not be NULL. But in your case you can have Unique constraint to store only unique/distinct values.

How to don´t repeat values stored in database

3 Answers3