Creating MySQL a table with a primary text key [duplicate]

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?

Answer 1

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);