Prevent duplicate values in database - mysql

Question

I have one table that store values about recurrence of some events, for example, the event with id=1 occurs in Monday, Wednesday and Friday, and the event with id=2 occurs in Monday and Wednesday. So my table is like this:

id  event_id  day
1      1      Monday
2      1      Wednesday
3      1      Friday
4      2      Monday
5      2      Wednesday

My problem is that sometimes (not always) my script insert duplicate values in my database, so my table have twice the recurrence of event 1 and 2, like show the following table:

id  event_id  day
1      1      Monday
2      1      Wednesday
3      1      Friday
4      2      Monday
5      2      Wednesday
1      1      Monday
2      1      Wednesday
3      1      Friday
4      2      Monday
5      2      Wednesday

If I add a constraint UNIQUE in column event_id the event 1 only have the value Monday but I want that has the rest of values like Wednesday and Friday. How to add a constraint to solve this?

Add a unique constraint on event_id and day combined instead of a unique constraint simply on event_id — Mark Baker
– Mark Baker, Commented Mar 29, 2015 at 22:53

Gordon Linoff · Accepted Answer · 2015-03-29 22:56:12Z

3

You want a unique index on event_id and day:

create unique index idx_recurrence_event_day on recurrence(event_id, day)

This will prevent the duplication that you do not want.

If you already have duplicates there are various ways you can get rid of them. However, it would be easier if you had a unique id for each row, which one reason why an auto-incremented primary key is useful in all tables.

One way to get rid of them even without a unique id is to use:

alter ignore table recurrence add unique (event_id, day);

Personally, I don't like this method because adding an index shouldn't delete rows, but this is a valid MySQL extension.

edited Mar 29, 2015 at 22:56

answered Mar 29, 2015 at 22:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:28:15Z

0

Mr. Linoff provided the right way but if you cant's modify your schema, you can also check on insert with row subquery:

insert into table_name
select 1, 2, 'Monday' from dual
where (1,2, 'Monday') not in
(select id,event_id, day from table_name);

SQLFIDDLE DEMO

Or use where not exists

insert into table_name
select 1, 2, 'Monday' from dual
where not exists
(select 1,2, 'Monday' from table_name);

SQLFIDDLE DEMO

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Mar 29, 2015 at 23:06

void

7,9503 gold badges29 silver badges47 bronze badges

2 Comments

seal Over a year ago

This is very useful too, but one problem that I forgot to mention in my question is that my database duplicate values, when a day passes , I don't know if my script have something wrong, but when I run the script the values inserted are correct.

void Over a year ago

yes of course there is other ways also, however as I mentioned the right way is right schema and creating unique index to prevent duplicate data, I just thought you my be have not access to create index.

Collectives™ on Stack Overflow

Prevent duplicate values in database - mysql

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related