Search code examples
mysqlconstraintsalter

How to ADD CONSTRAINT UNIQUE to colums that contains more characters than default

I want to add unique to the columns that is title, language and download (3 columns). Since i don't have other options to get rid of duplicate entries. Here is the code:

ALTER TABLE datapdf ADD CONSTRAINT tb_uq UNIQUE (title, language, download);

But i get this error:

1071 - Specified key was too long; max key length is 1000 bytes

Is there any solution for this? But remember, it should match by this three columns.

Solution

  • It seems that your this three columns' length are too long,I have do a test,look at it.

    drop table if exists datapdf;
create table datapdf 
(
title varchar(200),
 language varchar(300), 
 download varchar(510)
);

ALTER TABLE datapdf ADD CONSTRAINT tb_uq UNIQUE (title, language, download);

    I get an error the as same as your.

    Error Code: 1071. Specified key was too long; max key length is 767 bytes

    But if I change the structure of table datapdf,it runs succeed.

    drop table if exists datapdf;
create table datapdf 
(
title varchar(100),
 language varchar(100), 
 download varchar(100)
);

ALTER TABLE datapdf ADD CONSTRAINT tb_uq UNIQUE (title, language, download);

    This is the Action.

    ALTER TABLE datapdf ADD CONSTRAINT tb_uq UNIQUE (title, language, download)

    So I think you can change your table's structure(if possible) to solve this problem.However,it's not a good solution,because you may lose your data.One of the best solution is what @N.B said(the first comment of this question):"
    Add a fourth column. Save the hash of the 3 columns combined. Make the column unique."