mysql - How do I add unique constraint for 2 columns without deleting the existing duplicated records? -

i had table, col1 col2 col3, col1 primary key, want add unique constraint col2 , col3, used alter table add constraint, seems there duplicated records in table, have delete them make work, but, if need keep existing records, how can make sure newly added records unique constraint in col2 , col3?

if understand correctly, want leave current duplicates want insure don’t add new duplicates, right? follow comments above, understand if doesn’t meet business needs.

unfortunately constraint not meet needs describe them. may want control duplicate logic in software inserting data, not @ table level. instance following add row if val3 not exist in table. make sense?

insert mytable (col1, col2, col3, col4) values (val1, val2, val3, val4) not col3 = val3; 

or can fire instead of trigger. sql has special temporary ‘inserted’ table can compare against right before values go in. say, “insert these values, wait! insert them if rows in temp ‘inserted’ table don’t match existing rows in mytable."

create trigger no_duplicates_in_mytable on mytable instead of insert begin set nocount on if (not exists (select m.val3       mytable m, inserted       m.val3 = i.val3))    insert mytable       select val1, val2, val3, val4       inserted end 

or can handle logic @ table level after insert trigger, it’s little wonky. delete existing duplicates, in cases. this;

create trigger delete_duplicates_mytable after insert on mytable begin    delete mytable     val3 not in (select min(val3)                               mytable                               val1 = new. val1)    , val1 = new. val1; end; 

let me know if have questions or if not meet business needs.

edit: @rik correct, these insert triggers not work updates, allowing anyone's code change value duplicate. try update triggers. can noodle around these changing insert update.