Allowed duplicate record if data not exist in different column

594 views mysql
1

I have 2 column name location_id and district_id. location_id record can be duplicated if existing location_id and district_id record does not match. The table below will explain it.

enter image description here

Notice the duplicate record of location_id and district_id. This table has valid data.

How can I UPDATE with mysql while preventing duplicate. With below syntax, my table can be updated even if B008 and 01010244 already exist.

    UPDATE 
    `location_id`, district_id 
    SET
      `location_id` = 'B008',
      `district_id ` = 0101031504
    WHERE `id` = 5

My second query above will update even though existing record with similar data already existed.

Please assist and thanks in advance.

answered question

1 Answer

1

You can solve this problem structurally, by adding a Composite Unique constraint on the location_id and district_id together. This will block any new DML operations trying to create duplicate rows for the combination.

ALTER TABLE your_table_name 
ADD UNIQUE `unique_location_district` (`location_id`, `district_id`)

posted this

Have an answer?

JD

Please login first before posting an answer.