Prevent duplicate row values

Question

I've done some digging and I can't find an effective way to prevent duplicate entries based on my needs. I need columns 2 (proj_id) and column 4 (dept_id) never to be the same, as each dept would only work on a project once. So, rows 1 and 4, 6 and 7, and 14 and 15 shouldn't be allowed. I'll keep digging as well.

summary_id  proj_id     hours_id    dept_id     date_entered
1   8   3   6   9/9/2012
2   2   2   6   9/9/2012
3   1   6   19  9/9/2012
4   8   3   6   9/9/2012
5   2   5   17  9/9/2012
6   7   2   5   9/9/2012
7   7   2   5   9/9/2012
8   2   5   17  9/9/2012
9   7   4   17  10/10/2012
10  3   6   1   10/10/2012
11  5   1   15  10/10/2012
12  4   4   3   10/10/2012
13  3   5   1   10/10/2012
14  8   2   13  10/10/2012
15  8   2   13  10/10/2012

You need a unique index on the two columns, see this question: http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql — George, Oct 09 '12 at 12:24
You can specify a pair of two attributes, columns that is, as a `KEY` (unique) in your case — Havelock, Oct 09 '12 at 12:24
Thanks everyone for the feedback! I love this forum, very helpful! — Mike, Oct 09 '12 at 12:48

Havelock · Answer 1 · 2012-10-10T07:19:11.963

1

Define an unique key on both columns

ALTER TABLE `your_table` ADD UNIQUE (`proj_id`, `dept_id`);

edited Oct 10 '12 at 07:19

answered Oct 09 '12 at 12:26

Havelock

6,913
4
34
42

score 1 · Accepted Answer · answered Oct 09 '12 at 12:35

1

Before applying unique combine key to your table, you have to remove duplicate records first then apply the following sql command:

ALTER TABLE your_table_name ADD UNIQUE (proj_id, dept_id);

answered Oct 09 '12 at 12:35

Mohammad Towhidul Islam

264
1
2
12

Exactly what I needed, thank you! I had to clean things up first obviously, but this was exactly it. :) – Mike Oct 09 '12 at 12:48

score 0 · Answer 3 · answered Oct 09 '12 at 12:34

Looks like you are new to php and mysql. So here's the easiest way to do it.

Log on to PHPMyAdmin
Select your DB and your table.
View the Structure of it (clicking the button on top of the screen).
Check the two fields (proj_id and dept_id) using the check box on the left.
At the bottom of the table you should find the the words "With selected" and in front of it some actions. Select the option to make "Primary".

Of course, if you have duplicate entries first delete them.

Prevent duplicate row values

3 Answers3