Check two fields no repeat in table when MySQL INSERT

Question

I have following table:

     table (id,
            longitude,
            latitude,
            longlat,
            address,
            description,
            kind,
            synonym,
            primary key(id)
      );

I need to check that fields longlat and description in inserting row are unique and there are no rows with same combination of longlat and description it the table, inserted before it.

How should I modify my query?

      "INSERT INTO yandex_social_objects (longitude,latitude,longlat,address,description,kind,synonym) VALUES (val_1),(val_2),(val_3)...(val_n)"

Possibly easiest to just add a unique key on those fields to the table — Kickstart, Mar 01 '13 at 09:29
yeah create a unique combination of both longlat & description — Raul, Mar 01 '13 at 09:30
But what if I try to insert another row, where `longlat` will be duplicate and `decription` - not? whether this row is inserted? — Paul, Mar 01 '13 at 09:42

score 4 · Accepted Answer · edited May 23 '17 at 12:03

4

Add a UNIQUE constraint on the combination so this never happens:

ALTER TABLE yandex_social_objects
  ADD CONSTRAINT longlat_description_UQ
    UNIQUE (longlat, description) ;

After that, all your Inserts into the table will check this combination for uniqueness and either succeed or fail.

You can use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE ... for different behaviour on unique key collisions. Check this answer for differences: INSERT IGNORE vs INSERT … ON DUPLICATE KEY UPDATE

edited May 23 '17 at 12:03

Community

1
1

answered Mar 01 '13 at 09:30

ypercubeᵀᴹ

113,259
19
174
235

I have an error `Specified key was too long; max key length is 767 bytes` with your query. What I'm doing wrong? – Paul Mar 01 '13 at 09:39
Oh, I guess the `description` or the `longlat` is too long. What are the datatypes? – ypercubeᵀᴹ Mar 01 '13 at 09:42
`longlat` is varcar(128) and `description` is varchar(256) – Paul Mar 01 '13 at 09:44
And is the table using InnoDB or MyISAM engine? – ypercubeᵀᴹ Mar 01 '13 at 09:45
You'll have to lower the length. I suppose you have InnoDB, so a change of the 256 to 255 will be enough. – ypercubeᵀᴹ Mar 01 '13 at 09:47
now there are now errors with your query now but if I try to insert duplicate value I get mysql error. How to modify insert-query to just ignore repeating row? – Paul Mar 01 '13 at 10:00
1

You can use INSERT IGNORE - http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – Kickstart Mar 01 '13 at 10:26

Check two fields no repeat in table when MySQL INSERT

1 Answers1