MySQL Insert (conditional) unique value once per user

Question

I wasn't sure how to explain this in the title, but what I have is a table like this:

user_id           | subscription_id
6                   12
6                   10
12                  6
4                   12

Each user can subscribe to all other users, but is it possible to prevent a user from subscribing to another user twice through a INSERT query?

As my subscription_id is not unique, this happens:

user_id           | subscription_id
6                   12
6                   12

And I want to avoid that. As far as I know INSERT IGNORE, INSERT UPDATE and ON DUPLICATE only works with unique keys.

They work on unique and primary keys actually; not sure about triggers. Bottom line, spanning primary key :) — Ja͢ck, Dec 06 '12 at 14:39

score 0 · Answer 1 · edited May 23 '17 at 10:34

0

You need to set up your database table to have a composite primary key for user_id AND subscription_id

That way each row has to be unique across both the columns.

edited May 23 '17 at 10:34

Community

answered Dec 06 '12 at 14:40

cowls

Oh I didn't realize it would be that easy! Thank you :) – Alex Dec 06 '12 at 14:51

Ja͢ck · Answer 2 · 2012-12-06T23:08:39.107

0

The only reliable and easy way to make sure that a tuple cannot occur more than once inside a single table is either:

The first two are roughly the same, but unique keys treat null values as distinct as well, so that might not work for you.

ALTER TABLE user_subscriptions ADD PRIMARY(user_id, subscription_id);

edited Dec 06 '12 at 23:08

answered Dec 06 '12 at 14:41

Ja͢ck

2 Answers2