Creating MySQL a table with a primary text key

Question

The command that I use to CREATE my TABLE is:

CREATE TABLE carts(order_id TEXT(14), items TEXT, shipping INT, price INT

I'd like to set 'order_id' as my primary key. I've tried to ALTER the TABLE with:

ALTER TABLE `carts` ADD PRIMARY KEY(order_id)

But that returns the error:

 #1170 - BLOB/TEXT column 'order_id' used in key specification without a key length

I understand that means that the length isn't being set correctly in the initial setup, so I tried:

ALTER TABLE `carts` ADD PRIMARY KEY(order_id(14))

Which returns the same error. The type defined in phpmyadmin is 'tinytext'; I was expecting to see TEXT(14).

I'm performing all of these commands via PDO in PHP. What's the correct way to set the column 'order_id' as my TABLE's primary key?

Change `text` to `varchar`. you can't set a text as a primary key. — Linga, Feb 11 '14 at 11:37
Your last statement is well, it will create primary key with length 14. So what's the error? — Alma Do, Feb 11 '14 at 11:37
`order_id` TEXT?? If your order_id are characters please make it a `VARCHAR` Don't use TEXT for this. — Timmetje, Feb 11 '14 at 11:38
Why aren't you using VARCHAR as data type for ordeR_id column? There is a restriction on creating indexes for TEXT/LOB data types in MySQL and that is why you're getting the error — Incognito, Feb 11 '14 at 11:40
the table i was using require the id to be more than 20 character for which i was force to choose text and its working fine but I want to know if this is stable and reliable as the id is not autoincrement but timestamp(milisecond) and some other postfix added for info purposes. — R T, Aug 03 '17 at 19:21

score 10 · Answer 1 · answered Feb 11 '14 at 11:42

in MySQL BLOB/TEXT are not served as a part of table, rather they are referenced from other memory locations. So they cannot be treated as a part of KEY.

You have to define it as VARCHAR(14).

ALTER TABLE `carts` modify order_id VARCHAR(14);

And then you can apply the PK.

ALTER TABLE `carts` ADD PRIMARY KEY(order_id);

Creating MySQL a table with a primary text key

1 Answers1