在有效插入和更新时检查唯一性

I have 2 columns in a staff table that each column values has to be unique (staff_code, staff_name)

|  staff_id   | staff_code | staff_name |
|-------------|------------|------------|
| 1           | MGT        | Management |
| 2           | IT         | IT staff   |

When inserting or updating an item to the table I have to check whether staff_code is and staff_name is unique.

In the current procedure I use 4 functions to check it.

1. Check staff_code when inserting,
2. Check staff_code when updating
3. Check staff_name when inserting
4. Check staff_name when updating

I have included the simplified code

The function for checking 1)

$SELECT * FROM staff WHERE staff_code = $staff_code
if num_of_rows > 0 //cannot insert, staff_type already exists

The function for checking 2)

get current staff type from

$current_staff_type => SELECT * FROM staff WHERE staff_id = $staff_id

if($current_staff_type == $updated_staff_type){
    //don't update
    return
}

SELECT * FROM staff WHERE staff_type = $updated_staff_type

if(num_of_rows > 0){
    //the type you're going to update already exists
    return
} else{
    //update
}
if num_of_rows > 0 //cannot insert, staff_type already exists

I have similar functions for other 2 as well. The thing is I have to send the error messages separately for the 4 conditions. Is this the best practice to do this? Can I do it another simple way than accessing database several times?

• Add unique indexes as appropriate
• Perform Insert/Update
• Trap exceptions
• On Exception, run your functions to figure out what's duplicate

Basically, let the database enforce uniqueness and get details on the exception after it has been thrown. That will minimize calls to the database. Even if you wrap all this logic in a procedure call, you still want to Insert/Update first, and handle exceptions. That's the efficient way to do it, especially if the Insert/Update succeed the majority of the time.

Unique index as mentioned:

ALTER TABLE staff ADD UNIQUE INDEX code_name (staff_code,staff_name)

Always catch the error "ERROR: duplicate key value violates unique constraint" which happens when have attempted to violate this constraint.