Avoiding duplicate rows being inserted in where unizue rows are obtained from two tables

Question

I have a two tables such as customer_name and customer_phone, but the unique customer is identified from the combination of all the four columns from two of the tables.

Since we have multiple souce systems inserting into the below tables at the same time, in all those jobs we validate before insert using a function to check if the customer already exist using (f_name,l_name,area_code,phone_num) this combination. However we still see duplicates getting inserted, because the validation happens while other job has already inserted but not yet commited. Is there any solution to avoid duplicates ?

customer_name

Col: ID, First_name, last_name

cutomer_phone

col: ID,area_code, Phone_number

Answer 1

Yes. Don't do the checking in the application. Let the database do the checking by using unique indexes/constraints. If I had to guess on the constraints you want:

create unique index idx_customer_name_2 on customer_name(first_name, last_name);

create unique index idx_customer_phone_2 on customer_phone(customer_id, phone_number);

The database will then do the checking and you don't have to worry about duplicates -- although you should check for errors on the insert.