1

I am trying to prevent duplicate entries like this

INSERT IGNORE INTO myTable( `val_1`, `val_2`, `val_3`, `date` ) 
VALUES ( '$var_1', '$var_2', '$var_3', now() )

The values i would like to check are the 3 val_x but because now() will be a unique value, insert ignore does not work.

How can i not check that last variable as unique?

note: this is kind of like a cart, so i cannot make the first 3 values unique. There is a session variable that allows each user to see a a unique collection.

From this diagram, the first 2 rows are duplicate since they belong to the same user session. The 3rd row is not a duplicate becase it belongs to a different user session

 +---------+-------+--------+
 | session | var 1 | var 2  |
 +---------+-------+--------+
 | abc1234 | aaaaa | bbbbb  |
 +---------+-------+--------+
 | abc1234 | aaaaa | bbbbb  |
 +---------+-------+--------+
 | 5678def | aaaaa | bbbbb  |
 +---------+-------+--------+
 | 5678def | aaaaa | ccccc  |
 +---------+-------+--------+

as paqogomez suggested i removed now() from the query and altered the table but it looks like i need a primary key for insert ignore to work, but for my scenario i cant make these 3 columns unique

ERROR 1062: Duplicate entry 'aaaaa' for key 'var 1'

t q
  • 4,593
  • 8
  • 56
  • 91

3 Answers3

2

Create a unique index on the first three columns:

create unique index myTable_session_val2_val3 on myTable(session, val_1, val_2);

This will guarantee that combinations of these three are unique, without taking into account any other columns.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thank you for the response but i updated my question. I cannot make those 3 columns unique because this will act like a cart where different people will have collections based on a session variable. – t q Mar 19 '14 at 00:40
  • @tq . . . If there is a session variable, how are you setting it in the table? And, you can just add the session variable into the index. – Gordon Linoff Mar 19 '14 at 00:51
  • a session variable from php – t q Mar 19 '14 at 01:02
  • @tq Gordon's answer, in combination with what you've done with mine will get you the results you need. Try it out. (+1 gordon) – crthompson Mar 19 '14 at 01:46
  • i still cannot make the columns unique `ERROR 1062: Duplicate entry 'aaaaa' for key 'var 1'` – t q Mar 19 '14 at 16:10
  • 1
    @tq . . . That means that your existing data doesn't meet the criteria you seem to need for this table. – Gordon Linoff Mar 19 '14 at 19:30
  • @GordonLinoff i see, do you think i should post a new question? not sure how i would fix this. – t q Mar 19 '14 at 20:24
2

I would suggest moving the date into the default value of the column.

ALTER TABLE mytable CHANGE `date` `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

This way, you can still deal with the duplicates in the data in PHP. The alternative, as others have suggested would result in a database foreign key error if you attempted to insert a duplicate.

Then this sql would work and give the same result:

INSERT IGNORE INTO myTable( `val_1`, `val_2`, `val_3` ) 
VALUES ( '$var_1', '$var_2', '$var_3' )

EDIT:

You still need a unique index to make it work. See @Gordon's answer

crthompson
  • 15,653
  • 6
  • 58
  • 80
  • hmm, insert ignore is still inserting – t q Mar 19 '14 at 01:04
  • I'm confused. You want to make sure the values you are inserting are not duplicates, but you cannot make an unique index? – crthompson Mar 19 '14 at 01:11
  • sorry for the confusion, 1 of the column is a php session, based on this column the other columns cannot duplicate. If `column A` is a session for user A then the other columns can overlap with user B because their session variable is different – t q Mar 19 '14 at 01:14
  • @tq a composite key will exactly handle that. it is one index across the three columns. – crthompson Mar 19 '14 at 01:33
  • @tq to illustrate, if you had a session var of `1` and `2` and `2` as your values, it would be unique. Changing session var to `2` with the same values would still be unique. – crthompson Mar 19 '14 at 01:44
1

You should probably define the UNIQUE keys for the combination of columns you don't want to be duplicated. So, don't specify column date as UNIQUE. Did you verify that? Define rest of three values as unique columns. It will probably work.

dhunju
  • 71
  • 3