Ryan403
Ryan403

Reputation: 11

SQL - How to prevent duplicate in specific condition?

In general, we can use unique key or primary key to prevent this, but in my case. I am creating an MyFavorite table like this:

---------------------------------------------------------
|   UserName       |  FavoriteLink                      |
---------------------------------------------------------
|   Ryan           |  http://www.google.com             |
---------------------------------------------------------
|   Ryan           |  http://www.yahoo.com              |
---------------------------------------------------------
|   Joyce          |  http://www.google.com             |
---------------------------------------------------------
|   Joyce          |  http://www.cnn.com                |
---------------------------------------------------------

So, each user can have a lot of favoritelinks, but they shouldn't have duplicate favoritelink, for example, Ryan shouldn't have two favoritelink for http://www.google.com. but for this table, FavoriteLink field may be duplicate, because both Ryan and Joyce, they all have favoritelink for http://www.google.com.

Here is the question: how can I insert data into this table without duplicate FavoriteLink for specific person?

Upvotes: 1

Views: 147

Answers (2)

Amadan
Amadan

Reputation: 198304

Composite keys.

CREATE TABLE userlinks (
  user VARCHAR(255),
  link VARCHAR(255),
  PRIMARY KEY (user, link)
)

or

CREATE TABLE userlinks (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  user VARCHAR(255),
  link VARCHAR(255),
  UNIQUE KEY (user, link)
)

depending on what exactly it is you want.

Upvotes: 4

mhitza
mhitza

Reputation: 5715

You can add a composite unique index.

CREATE UNIQUE INDEX username_favorite_uniq ON yourTable (UserName, FavoriteLink)

Upvotes: 3

Related Questions