Insert or update (if exists) without primary key

Question

I have the following database MySQL table.

• id (PK, AI)
• email
• country
• lastlogin

I have a regular query in PHP that inserts this into the table. however, logically, if this code runs several times, the same row will be inserted to the database every time.

I want my reference for checking and duplication to be the email field, and if the email is the same, update the country and the lastlogin.

I checked on other questions for a similar issue and the suggested way was to use ON DUPLICATE KEY like this

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

However, my primary key is not the email field rather the id but I don't want to run the check on it.

Answer 1

One option is make the email field unique, and then it should behave the same as primary key, at least with regard to MySQL's ON DUPLICATE KEY UPDATE:

ALTER TABLE yourTable ADD UNIQUE INDEX `idx_email` (`email`);

and then:

INSERT INTO yourTable (email, country, lastlogin)
VALUES ('[email protected]', 'value2', 'value3')
ON DUPLICATE KEY UPDATE
    email='value1', country='value2', lastlogin='value3'

If the email [email protected] already exists in your table, then the update would kick in with alternative values.

From the MySQL documentation:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

This approach doesn't only work with primary keys, it also works with any column having a unique index.