Heider Kumar Heider Kumar - 4 months ago 9
SQL Question

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.

Answer

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.