How avoid duplicate record if you have a table with "autoincrement" ID column

Developers Databases
#1

I did lot of test but still have the problem that I’m not able to avoid duplicate record.
The existing index do not block duplicate (???).
I don’t know how to fix it .

My mySQL table has an ID field
memberID int(11) No None AUTO_INCREMENT
…
…
and these two INDEXes (1st primary and 2nd unique)
PRIMARY__________________BTREE S? No memberID 1 A
key_Name_Year_Meccan_____BTREE S? No contactLastName 1 A
_____________________________________________memberName 1 A
_____________________________________________year_card_actual 1 A
_____________________________________________national_federationID 1 A

This is my query for upload new records or update existing ones (with same index key_Name_Year_Meccan). No way it wants to work ;-((

$sql_insert_update_members = "
INSERT IGNORE INTO members(
officeID , section_ID , registration_typeID , new_renewal , memberName , contactLastName , userName , titleID , borndate , bornplace , borncountry , sexID , citizenshipID , phone , fax , mobile01 , mobile02 , addressLine1 , addressLine2 , e_mail , fbname , twname , skype , image , city , state , postalCode , country , vatcode , residence , fiscalcode , national_federationID , medical_date , subscr_date , year_card , year_card_actual , member_card , license_card_01 , license_card_02 , license_card_03 , license_card_04 , license_card_05 , affiliated01 , affiliated02 , affiliated03 , affiliated04 , affiliated05 , category_01 , category_02 , shirt_name , shirt_number , shirt_size , pants_size , helmet_size , boots_size , gloves_size , insert_date
)

SELECT
officeID , section_ID , registration_typeID , new_renewal , memberName , contactLastName , userName , titleID , borndate , bornplace , borncountry , sexID , citizenshipID , phone , fax , mobile01 , mobile02 , addressLine1 , addressLine2 , e_mail , fbname , twname , skype , image , city , state , postalCode , country , vatcode , residence , fiscalcode , national_federationID, medical_date , subscr_date , year_card , year_card_actual , member_card , license_card_01 , license_card_02 , license_card_03 , license_card_04 , license_card_05 , affiliated01 , affiliated02 , affiliated03 , affiliated04 , affiliated05 , category_01 , category_02 , shirt_name , shirt_number , shirt_size , pants_size , helmet_size , boots_size , gloves_size , NOW()
FROM members_fim_csv

ON DUPLICATE KEY
UPDATE
members.new_renewal = members_fim_csv.new_renewal ,
members.borndate = members_fim_csv. borndate ,
members.sexID = members_fim_csv.sexID ,
members.citizenshipID = members_fim_csv.citizenshipID ,
members.phone = members_fim_csv.phone ,
members.fax = members_fim_csv.fax ,
members.mobile01 = members_fim_csv.mobile01 ,
members.addressLine1 = members_fim_csv.addressLine1 ,
members.e_mail = members_fim_csv.e_mail ,
members.city = members_fim_csv.city ,
members.residence = members_fim_csv.residence ,
members.subscr_date = members_fim_csv.subscr_date,
members.update_date = NOW()
";

#2

First of all, what do you want to do: INSERT IGNORE … or INSERT … ON DUPLICATE KEY UPDATE ?
I suppose you want to update in case the record exists, so remove your IGNORE and see what the result is.
I would recommend to start with a plain INSERT…

If you don’t get an error message and the command inserts duplicate rows, there is something wrong with your indices. Simple as that.
Are both table definitions identical (field_type, field_size), are you sure both sides have the same content? (space character!).

jsb

#3

Hi,
thanks,
Yes, you are right but … you know…to be sure … I’ve exagerate!!!
Incredible but, after a testing day, the problem was connected to a csv field that was imported with a blank space before the number … an so it did not finded the same value and it went to insert a new , …almost, identical record.
Now I’ve trimmed the field and now also index seems to work without making, as expected, any the duplicate.
Based on your hind I deleted IGNORE.
Just to know , why it worked also with both ??

#4

It’s because the IGNORE statement ignores the error of a duplicate key and does not abort with an error message so the ON DUPLICATE KEY UPDATE finishes the operation.
Since you want to update anyway there is no need for the IGNORE statement because the ODKU clause also checks for duplicate keys (as the name suggests).

jsb

#5

attachment

2 first images are about what happens and last one it’s my goal

copy.jpg