-1

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

Vinal S
  • 1
  • 1
    I fail to see how the records are linked. How do you know which First_Name, Last_Name belongs to which area_code, Phone_number? – Thorsten Kettner Jun 24 '15 at 09:58
  • thorough an unique ID column in both tables.. – Vinal S Jun 29 '15 at 13:25
  • Well, a column called ID is usually the ID to uniquely identify the table''s records. But you are using it differently. So I must ask further: Is ID unique in customer_name, i.e. do I get no more than one name for one ID? And is ID unique in customer_phone, i.e. do I get no more than one phone number for one ID? – Thorsten Kettner Jun 29 '15 at 13:36
  • the ID in the customer_phone table is the foreign key from the customer_name table... basically thts how we can relate this phone number belongs to this customer... – Vinal S Jun 29 '15 at 13:56
  • So a customer has one record in customer_name and zero, one more more records in customer_phone. And you want to avoid to insert John Smith 0044 123456 again, if he is already present in the database. But what if he isn't? How do you find out whether he is the same John Smith who has number 0044 012345 already or a new John Smith? In the first case you would have to enter the new phone number with the existing ID, in the second you would have to get a new ID and insert both name and phone. – Thorsten Kettner Jun 29 '15 at 14:12

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • nope f_name and l_name are not unique.. only joining wiht the phone_number u get a unique data.... – Vinal S Jun 24 '15 at 10:22
  • @VinalS . . . You don't specify what the duplicates are in the question. That is just an example. The key idea is to use unique indexes. – Gordon Linoff Jun 24 '15 at 11:08
  • Im inserting (TableA.f_name,TableA.l_name,Table_B.area_code,Table_B.phone_num) as duplicate.... eg.. if i'm running the below insert statements from two different sessions at the same instance only changing the ID.. it still inserts duplicate customer details which should not be the case.. `code` Insert into customer_name(id,f_nm,l_nm) values (12345,'SMITH','JONES') ; Insert into customer_phone(id,ar_cd,ph_no) values(12345,604,4999999); `code` – Vinal S Jun 29 '15 at 14:08