checking uniqueness when inserting and updating efficiently

Question

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.

Check staff_code when inserting,
Check staff_code when updating
Check staff_name when inserting
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?

Does `staff_name` really have to be unique? There will be people working in the same place with the same name. — James, Aug 13 '18 at 16:27
`SELECT * FROM staff WHERE staff_type = $updated_staff_type OR staff_code = $staff_code` check once with `OR` — bassxzero, Aug 13 '18 at 16:28
You could also have a clause in the insert SQL to only do it if not exists, then check the result if was inserted or not, this saves an initial query before even trying to insert — James, Aug 13 '18 at 16:29
indeed @Sakezzz is right your current approach is prone to a condition which is called **race condition** — Raymond Nijland, Aug 13 '18 at 16:30
I can't use OR condition because I have to return error messages separately for the user for each conditions — Tharindu, Aug 13 '18 at 16:31
In addition to unique indexes the `ON DUPLICATE KEY UPDATE` option of an `INSERT` allows you to insert if new and update if already there very easily. — Dave, Aug 13 '18 at 16:31
"management" is not a staff_name, it's a department, or group, or team, or something else. This is a little off the subject but just FYI — James, Aug 13 '18 at 16:32
https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — James, Aug 13 '18 at 16:34
Thank you for the suggest Dave. I'm just new to this db and It took some time for me to understand it — Tharindu, Aug 13 '18 at 16:36
Basic questions like these are always duplicates, please continue researching. I'll recommend prepared statements for stability/security. Please also read: https://dev.mysql.com/doc/refman/8.0/en/replace.html — mickmackusa, Aug 13 '18 at 16:45

score 2 · Accepted Answer · answered Aug 14 '18 at 00:47

2

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.

answered Aug 14 '18 at 00:47

danblack

12,130
2
22
41

score 1 · Answer 2 · answered Aug 13 '18 at 18:00

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.

checking uniqueness when inserting and updating efficiently

2 Answers2

Linked