2

Im curious if there is a way to insert into MySQL and avoid duplicates. I know there are three primary ways of accomplishing it. Either Replace, insert ignore or insert on duplicate key. I believe that each of these rely on a unique primary key which for me is a auto increment id field. This id field does not have any value for my particular inserting needs instead i want to check if a few fields contain the same value if not dont insure

ex:

INSERT INTO table (name, team, email) VALUES (:n, :t, :e)

if i had three data sets like so

1) array (":n" => "john doe", ":t" => 1, ":e" => john@doe.com);
2) array (":n" => "Jane doe", ":t" => 1, ":e" => john@doe.com);
3) array (":n" => "john doe", ":t" => 1, ":e" => john1@doe.com);
4) array (":n" => "john doe", ":t" => 2, ":e" => john1@doe.com);

Assuming there are no values in the table

1, 3 and 4 would be inserted but 2 would not as the team and email are not unique

I am aware i could create a fourth column and add the email and team to it and create a unique value and use this however i would prefer not to do so as it is redundant data .

Thanks

3 Answers3

3

You can create an UNIQUE constrain 

 ALTER TABLE `tableName` ADD UNIQUE `unique_idx`(`name`, `team`, `email`);
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
1

If the id field has no value for you, don't bother with it. The desired behavior you're describing is that of a composite key, so make a composite key.

First, scrap the one that exists and is of no value...

ALTER TABLE YourTableName DROP PRIMARY KEY;

Then create the new composite one...

ALTER TABLE YourTableName ADD PRIMARY KEY (name, team, email);

Community
  • 1
  • 1
LDMJoe
  • 1,591
  • 13
  • 17
0

You can try using DUAL

INSERT INTO table (name, team, email)
SELECT :n, :t, :e
FROM DUAL
WHERE :t NOT IN (SELECT team FROM table WHERE email = :e)
genespos
  • 3,211
  • 6
  • 38
  • 70