CODEWITHSUNDEEP
mysql
Paul
Paul

Reputation: 6737

Check two fields no repeat in table when MySQL INSERT

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)"

Upvotes: 0

Views: 146

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

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

Upvotes: 4

Related Questions