Using MySQL IGNORE on one column under certain conditions

insert, mysql, php, sql

Solution

What you are trying to achieve is not very clear to me, but it looks very much like you just need to add some two-columns unique constraints.

- an `email` must be unique for one given `account_id`:

ALTER TABLE your_table ADD UNIQUE (account_id, email);

- a `phone number` must be unique for one given `account_id`:

ALTER TABLE your_table ADD UNIQUE (account_id, phone);

Both indexes may exist at the same time on your table. Either could raise a "duplicate-key violation" error, and would trigger the `IGNORE` or the `ON DUPLICATE` clauses of your insertions.

That being said, there is an issue in your structure. You are about to duplicate your customers' details for each `account_id` they are in business with.

You should have a `customers` table that contains all your customer's contact details (and only that), another `accounts` table -- your "offices", if I understand it right -- and finally one relation table to model the n-n relationship between `customers` and `accounts`:

CREATE TABLE customers_accounts (
    customer_id INT NOT NULL,
    account_id INT NOT NULL,
    PRIMARY KEY (customer_id, account_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

Problem

I want to import records from Gmail into a table, and I do not need duplicates for each account. Description: I have a table named list with definition below: ``` id int(11), account_id int(11), email varchar(255), phone varchar(30), primary key(id), FOREIGN KEY (account_id) REFERENCES accounts (id) ``` This table holds records for different accounts and an email can be considered valid for two or more accounts. This means that an email can repeat in a table but can only appear once for each account_id. I imported my contacts from Gmail (which is above 700 contacts and other users may have more than that). The challenge: I have an option of running two queries (one to check if email or phone exists, the second to insert record) for each record which in my case is 1,400 SQL queries to enable me insert all imported records, ensuring there are no duplicates for each account_id in the list table. I have looked at MySQL IGNORE and similar keywords like ON DUPLICATE KEY UPDATE but they do not seem to work in this scenario as I cannot make the email and phone columns unique as they can contain duplicate content. What is the best way of inserting these 700 records ensuring that the email and phone are not repeated for each account_id without having to run 1,400 queries? QUESTION UPDATE: I do not think INSERT IGNORE CAN WORK HERE FOR THE FOLLOWING REASONS: - I cannot make email and phone unique columns - The phone number may be empty but with an email entry, this may break the unique pattern QUESTION ILLUSTRATION I have two offices using the table to store their customer records. Someone can be a customer to both offices. This means his record can appear twice in the table but can only appear once for each account_id in the table. The challenge now is to insert several records into the table ensuring that a record does not repeat for each account_id.

Original source