3

I'm trying to create a table, but I get this error:

SQL error - BLOB/TEXT column 'url' used in key specification without a key length

This is the SQL I'm using

CREATE TABLE recipes (
url         TEXT  NOT NULL,
name        TEXT  NOT NULL,
pic_url     TEXT  NOT NULL,
vegetarian  INT   NOT NULL,
ingredients TEXT  NOT NULL,
time        INT   NOT NULL,
rating      FLOAT NOT NULL,
ratings_no  INT   NOT NULL,
keywords    TEXT  NOT NULL,
UNIQUE (
    url
)
);

Does anyone know how I can fix this? Thanks.

David Kamer
  • 2,677
  • 2
  • 19
  • 29
Lucas
  • 31
  • 3

2 Answers2

4

You may only index the first columns of a TEXT or BLOB column.

UNIQUE (url(512))

will do the trick, creating an index on the first 512 characters of the column.

But you should avoid using TEXT columns unless absolutely necessary, and almost never with indexes. They go into separately allocated objects in the DBMS, which makes insertions, updates, and queries slower. VARCHAR(2048) or a datatype like that will perform better. And stuff like unique indexes work correctly. Please read this. https://dev.mysql.com/doc/refman/8.0/en/blob.html

Notice that URLs work best when limited to about 2000 characters in length.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
1

BLOB / TEXT are of length 65535. Indexes in MySQL are considerably less.

Recommend using a VARCHAR(length) (latin character set) of a length appropriate to a URL.

danblack
  • 12,130
  • 2
  • 22
  • 41