Enforce unique rows in MySQL

Question

I have a table in MySQL that has 3 fields and I want to enforce uniqueness among two of the fields. Here is the table DDL:

CREATE TABLE `CLIENT_NAMES` (
`ID` int(11) NOT NULL auto_increment,
`CLIENT_NAME` varchar(500) NOT NULL,
`OWNER_ID` int(11) NOT NULL,
PRIMARY KEY  (`ID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The ID field is a surrogate key (this table is being loaded with ETL). The CLIENT_NAME is a field that contains names of clients The OWNER_ID is an id indicates a clients owner.

I thought I could enforce this with a unique index on CLIENT_NAME and OWNER_ID,

ALTER TABLE `DW`.`CLIENT_NAMES` 
ADD UNIQUE INDEX enforce_unique_idx(`CLIENT_NAME`, `OWNER_ID`);

but MySQL gives me an error:

Error executing SQL commands to update table. Specified key was too long; max key length is 765 bytes (error 1071)

Anyone else have any ideas?

Answer 1

MySQL cannot enforce uniqueness on keys that are longer than 765 bytes (and apparently 500 UTF8 characters can surpass this limit).

1. Does CLIENT_NAME really need to be 500 characters long? Seems a bit excessive.
2. Add a new (shorter) column that is hash(CLIENT_NAME). Get MySQL to enforce uniqueness on that hash instead.