CODEWITHSUNDEEP
mysql
Evelyn1986
Evelyn1986

Reputation: 159

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

I had this table, col1 col2 col3, col1 is the primary key, now I want to add unique constraint for col2 and col3, i used alter table add constraint, but it seems there were already duplicated records in the table, so I have to delete them to make it work, BUT, if I need to keep all the existing records, how can i make sure that the newly added records are unique constraint in col2 and col3?

Upvotes: 1

Views: 2224

Answers (2)

BClaydon
BClaydon

Reputation: 1970

If I understand correctly, you want to leave the current duplicates but you want to insure you don’t add any new duplicates, right? Personally I would follow the comments above, but I understand if that doesn’t meet your business needs.

Unfortunately a constraint does not meet your needs as you describe them. You may want to control the duplicate logic in the software inserting the data, not at the table level. For instance the following will only add the row if val3 does not already exist in the table. Make sense? 

INSERT INTO MyTable (col1, col2, col3, col4)
VALUES (val1, val2, val3, val4) WHERE NOT col3 = val3;

OR you can fire an INSTEAD OF trigger. SQL has a special temporary ‘inserted’ table that you can compare against right before your values go in. So you say, “Insert these values, BUT WAIT! Only insert them if the rows in the temp ‘inserted’ table don’t match the existing rows in MyTable."

CREATE TRIGGER no_duplicates_in_MyTable ON MyTable
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF (NOT EXISTS (SELECT M.val3
      FROM MyTable M, inserted I
      WHERE M.val3 = I.val3))
   INSERT INTO MyTable
      SELECT val1, val2, val3, val4
      FROM inserted
END

OR you can handle the logic at the table level with an AFTER INSERT trigger, but it’s a little wonky. You could delete the existing duplicates, in some cases. Something like this;

CREATE TRIGGER delete_duplicates_MyTable
AFTER INSERT ON MyTable
BEGIN
   DELETE FROM MyTable 
   WHERE val3 NOT IN (SELECT MIN(val3) 
                             FROM MyTable 
                             WHERE val1 = new. val1)
   AND val1 = new. val1;
END;

Let me know if you have any questions or if this does not meet your business needs.

EDIT: @Rik is correct, these are INSERT triggers so they will NOT work for updates, allowing anyone's code to change a value to a duplicate. Try UPDATE triggers. You can noodle around with these changing INSERT to UPDATE.

Upvotes: 1

fancyPants
fancyPants

Reputation: 51868

Personally I'd go along with the other's comments. You should really fix the data, then add the constraints.

However, just for the sake of it, a possible solution could be to use the MERGE engine. Basically you'd keep the data you have now in one table and create an almost identical second table. Only difference, the index is unique this time.

See this example:

CREATE TABLE t1 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20),
   key whatever (message)
   ) ENGINE=MyISAM;

CREATE TABLE t2 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20),
   unique key whatever (message)
   ) ENGINE=MyISAM;

INSERT INTO t1 (message) VALUES ('Testing'),('Testing'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE total (
   a INT NOT NULL AUTO_INCREMENT,
   message CHAR(20), INDEX(a))
   ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

select * from total; 
/*Here you can see, that there are duplicate values*/

insert into total (message) values ('Testing');
/*but this results in a duplicate key error*/

insert into total (message) values ('it work\'s');
/*whereas this doesn't*/

Read more about the MERGE engine here.

Upvotes: 0

Related Questions