CODEWITHSUNDEEP
phpmysql
Mike
Mike

Reputation: 2761

Prevent duplicate row values

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

Upvotes: 1

Views: 174

Answers (3)

Havelock
Havelock

Reputation: 6966

Define an unique key on both columns 

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

Upvotes: 1

Mohammad Towhidul Islam
Mohammad Towhidul Islam

Reputation: 264

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);

Upvotes: 1

itsols
itsols

Reputation: 5582

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

  1. Log on to PHPMyAdmin
  2. Select your DB and your table.
  3. View the Structure of it (clicking the button on top of the screen).
  4. Check the two fields (proj_id and dept_id) using the check box on the left.
  5. 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.

Upvotes: 0

Related Questions