How to ensure that MySQL rows are unique?

Question

I am trying to insert a row into a MySQL table, with the condition that the row itself has to be unique. So for example, the row would have id, name and address, but we can't have an insert with duplicate name and address. I tried doing a select before inserting, to make sure I don't find the result, but it doesn't seem to be working. I am currently trying to do this before inserting:

SELECT * FROM locations WHERE cityId = $cityId AND countyId = $countyId AND stateId = $stateId AND countryId = $countyId AND district = $district;

EDIT: All those fields are allowed to duplicate, they just can't duplicate all at the same time.

Answer 1

The cleanest solution would be to put a UNIQUE constraint on cityId, countyId, stateId, countryId, districtId and then check whether the insert statement was successful.

To quickly test this:

ALTER TABLE your_table_here ADD UNIQUE INDEX(cityId, stateId, countryId, countyId, districtId);

And rerun the insert query. If it encounters a duplicate it will fail, which you will be able to handle in your application.

Answer 2

Assuming you already have a unique index set on the table you can use:

INSERT IGNORE INTO locations SET cityId = $cityId, countyId = $countyId, stateId = $stateId, countryId = $countyId, district = $district;

Answer 3

with such constraints, you should have made the name and address columns as composite primary key..