0

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
  • Run SHOW CREATE TABLE cb_company ; and edit the questtion with the output – ypercubeᵀᴹ Apr 13 at 21:35
  • Added the output above. – keval Apr 14 at 5:35
  • Im guessing you need to add the column and then define it as the primary key. – Sir Swears-a-lot Apr 14 at 7:29
  • 1) UUID() is the name of built-in function. So always wrap UUID field name with backticks; 2) TEXT datatype column cannot be used for index create without specifying the prefix keypart length. – Akina Apr 14 at 7:54
  • PS. The best datatype for storing UUID values is CHAR(36). – Akina Apr 14 at 8:33
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.