Set Unique pair of columns in a MySQL table

Question

I am working on project with php-MySQL in the back-end.It has various articles to browse,sorted into various categories and a user-login system.
I recently added a watch-list functionality to it which enables users to add articles to their watch-list/dashboard.
I have the following tables.

Articles table
article-id(int) Primary-Unique
cat(varchar)
name(varchar)
subCat(varchar)
description(varchar)
date added

users table
username(varchar) Primary-Unique
lname
fname
joined-date

.Watchlist table
article-id(int)
username(varchar)
date_watch

As we can see there is no unique key for watch-list table
I want to make (username + article-id) a unique pair Because for every username more than 1 article-id's exist and viceversa
I just want to insert and delete rows and It's not needed to update them
how to prevent duplicate entries?
till now I have been checking number of rows with php

"SELECT * FROM watchlist WHERE article-id={$id} AND username={$user}"

and if

mysql_num_rows() >1(not allowed to insert) 

This works fine but if by mistake that INSERT command is executed thare will be a duplicate entry
How to prevent it?
I am using phpmyadmin

Answer 1

You can simply add a unique key to the table:

ALTER TABLE `watchlist`
ADD UNIQUE INDEX `watchlist_unique` (`article-id`, `username`);

Also, looking at the functionality of what you have, you may as well opt for setting it as your primary key, by using this instead of the above:

ALTER TABLE `watchlist`
ADD PRIMARY KEY (`article-id`, `username`);

Both will prevent any insertion of a duplicate entry.

In addition, if you want to insert in this case, you may want to check out INSERT IGNORE and ON DUPLICATE KEY. For more info on both see "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE".