List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 22 2005 11:46am
Subject:Re: UNIQUE constraint, proper use
View as plain text  

> I have this SQL statement:
>
> CREATE TABLE rooms (
>                          id                SERIAL,
>                          room_name       TEXT UNIQUE,
>                          location      TEXT,
>                          last_updated  TIMESTAMP DEFAULT
> CURRENT_TIMESTAMP NOT NULL,
>                          last_user_updated    TEXT,
>              PRIMARY KEY(id)
> );
>
> When I run this I get the following error:
> ERROR 1170 (42000): BLOB/TEXT column 'room_name' used in key
> specification without a key length
>
> I'm more used to PostgreSQL and when I run this command there, I don't
> get this error message. I recognized that by changing room_name to a
> varchar type, I won't get the error message.

TEXT in MySQL, is a specific BLOB-type column. It isn't able to guarantee
uniqueness on the full blob. I wonder if that is what you want.

VARCHAR is not a blob and can be indexed (a unique constraint creates
an index automatically).

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thread
UNIQUE constraint, proper useFerindo Middleton Jr22 Nov
  • Re: UNIQUE constraint, proper usePaul DuBois22 Nov
  • Re: UNIQUE constraint, proper useHarald Fuchs22 Nov
  • Re: UNIQUE constraint, proper useMartijn Tonies22 Nov