Mysql – Alter table – existing column as primary key

alter-tableMySQLprimary-key

I am attempting to set one of the columns in the table as primary key.

Here are the columns from the table:

enter image description here

Now, when I run the sql:

ALTER TABLE cb_company 
ADD PRIMARY KEY(uuid);

I get the following error – 

Error Code: 1072. Key column 'uuid' doesn't exist in table

Here's the output of SHOW CREATE TABLE cb_company

CREATE TABLE `cb_company` ( `uuid` text,
                            `num_employees` text,
                            `name` text,
                            `aliases` text,
                            `short_description` text,
                            `primary_role` text,
                            `funding_total_usd` int(11) DEFAULT NULL,
                            `num_investments` text,
                            `homepage` text,
                            `num_founders` int(11) DEFAULT NULL,
                            `founders` text,
                            `num_investors` int(11) DEFAULT NULL,  
                            `investors` text,
                            `location` text,
                            `category_list` text,  
                            `num_funding_rounds` int(11) DEFAULT NULL
                          ) ENGINE=InnoDB 
                            DEFAULT CHARSET=utf8mb4  
                            COLLATE=utf8mb4_0900_ai_ci

Best Answer

A TEXT column cannot be in an INDEX (a PRIMARY KEY is an index). As Akina points out, uuids may as well be

uuid CHAR(36) CHARACTER SET ascii

Such is indexable. Before changing away from TEXT, running your ALTER will give you

ERROR 1170 (42000): BLOB/TEXT column 'uuid' used in key specification without a key length

You could change the datatype, then do the ALTER. Or if you are starting from scratch, 

CREATE TABLE `cb_company` ( `uuid` CHARACTER SET ascii,
                        `num_employees` text,
                        ...
                        PRIMARY KEY(uuid)
                      ) ENGINE=InnoDB 
                        DEFAULT CHARSET=utf8mb4  
                        COLLATE=utf8mb4_0900_ai_ci;

It is generally better to use VARCHAR with realistic limits.

If num_employees is a number, use INT.

If announced_on is a date, consider using DATE if you ever plan to compare or manipulate as a date.

Be aware that you will have to provide the uuid value. One way is

INSERT INTO cb_company (uuid, num_employees, ... )
     VALUES
     (UUID(), ...)

Then a problem is -- how to retrieve the value given a company name? Perhaps you also want some other index.

Etc.

Related Question