why cant i set varchar to be 1000 in length

if this matters, i also set it unique

The max I've managed to set for varc开发者_JAVA百科har is 745 (it's set to unique also).

---

Depends what version of MySQL you are running, before 5.0.3 there was a 255 character limit for varchar:

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

From: http://dev.mysql.com/doc/refman/5.0/en/char.html

---

Update:

Maybe it has to do with the maximum key length of 1000 bytes:

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

As an index is created for the key and as it is working without an index, this makes sense.
Nevertheless you can have an index on varchar columns bigger than 1000 bytes as you can read in my answer below.

Or if you are using InnoDB, then there are some restrictions:

Index key prefixes can be up to 767 bytes. See Section 12.1.8, “CREATE INDEX Syntax”.

---

I assume it is to ensure that the index doesn't get too large and works acceptable fast.

If it is very likely that a string column has a unique prefix on the first number of characters, it is better to index only this prefix, using MySQL's support for creating an index on the leftmost part of the column (see Section 12.1.8, “CREATE INDEX Syntax”). Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks. See Section 7.5.3, “Tuning Server Parameters”.

That means also that you can define the first n characters of such a column that should get indexed. Maybe this helps to circumvent the problem.

Example:

CREATE INDEX part_of_name ON customer (name(10));

---

Old mysql?

5 and newer can use long varchar

http://dev.mysql.com/doc/refman/5.0/en/char.html

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

---

A small visit around the MySQL manual should be useful: http://dev.mysql.com/doc/refman/5.0/en/char.html

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

An additional: Key values length can't exceed 500 characters. (Reported as of http://bugs.mysql.com/bug.php?id=2130)

---

Next step up in length would probably be text, though as far as I know that has some really huge in-database length limit, if any, so you'd have to provide limits in the programming sql/backend application logic.