How can I modify the size of Primary Key column of Varchar in a MariaDB table?

Question

I have

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

My table desc:

MariaDB [GTX_CC]> desc SubscribedUser;
+---------------------------------------------+--------------+------+-----+---------+-------+
| Field                                       | Type         | Null | Key | Default | Extra |
+---------------------------------------------+--------------+------+-----+---------+-------+
| UserID                                      | varchar(25)  | NO   | PRI | NULL    |       |
| UserName                                    | varchar(100) | YES  |     | NULL    |       |
| UserStatusCode                              | varchar(10)  | NO   |     | NULL    |       |
| Password                                    | varchar(512) | NO   |     | NULL    |       |

Now I want to Modify the Column length of UserID to varchar(999)

Running

MariaDB [GTX_CC]> ALTER TABLE SubscribedUser MODIFY UserID  varchar(999) NOT NULL  AUTO_INCREMENT;

gives the following error:

ERROR 1063 (42000): Incorrect column specifier for column 'UserID'

Added>

I also tried:

MariaDB [GTX_CC]> ALTER TABLE SubscribedUser MODIFY UserID  varchar(999) NOT NULL ;

It also gives error as:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Also Tried as follows:

MariaDB [GTX_CC]> ALTER TABLE SubscribedUser MODIFY UserID  varchar(767) NOT NULL ;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [GTX_CC]> ALTER TABLE SubscribedUser MODIFY UserID  varchar(766) NOT NULL ;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [GTX_CC]>

What wrong in my statement? Any help , please?

What are you trying to do here? If you want auto-increment then don't use `varchar` — juergen d, Oct 01 '18 at 07:36
Now I want to Modify the Column length of UserID to varchar(999) — Rup Rekha Logic, Oct 01 '18 at 07:38
999 is too long for a indexed column. 255 is max. Also use `int` instead of `varchar(...)` — juergen d, Oct 01 '18 at 07:43
MariaDB [GTX_CC]> ALTER TABLE SubscribedUser MODIFY UserID varchar(255) NOT NULL ; ERROR 1833 (HY000): Cannot change column 'UserID': used in a foreign key constraint 'fk_Session_SubscribedUser1' of table 'GTX_CC.UserSession' Now, stuck in other thing. — Rup Rekha Logic, Oct 01 '18 at 07:47
Is it not possible to have the modify cascade to the child table? — Rup Rekha Logic, Oct 01 '18 at 07:49
You have to turn foreign key checks off. then change both tables and turn it on again — juergen d, Oct 01 '18 at 07:51
I have the answer as: https://stackoverflow.com/a/13606801/10439752 — Rup Rekha Logic, Oct 01 '18 at 08:00

Tano Fotang · Answer 1 · 2018-10-01T13:29:14.740

What is wrong, appears to be related to this question: MariaDB won't create table with large VARCHAR as PRIMARY KEY.

In summary, and without wanting to repeat what's said in the above link, there is a maximum length for varchar primary keys, and the limit depends on the charset. To verify that, drop the primary key (alter table SubscribedUser drop primary key), alter the length of userid to your 999 (that succeeds), try to add userid as primary key (alter table SubscribedUser add primary key(userid)) and it'd fail with the same error.

score 0 · Answer 2 · answered Oct 01 '18 at 09:39

The script will be as follows:

LOCK TABLES 
UserSession WRITE,
SubscribedUser WRITE;

ALTER TABLE UserSession
DROP FOREIGN KEY fk_Session_SubscribedUser1,
MODIFY SubscribedUser_UserID varchar(255) NOT NULL;

ALTER TABLE SubscribedUser MODIFY UserID  varchar(255) NOT NULL ;

ALTER TABLE UserSession
ADD CONSTRAINT fk_Session_SubscribedUser1 FOREIGN KEY (SubscribedUser_UserID)
REFERENCES SubscribedUser (UserID);

UNLOCK TABLES;

The maximum allowed char length under varchar is 255 only.

score 0 · Answer 3 · answered Oct 12 '18 at 00:47

Please provide SHOW CREATE TABLE SubscribedUser. I question whether that 'describe' is correct. It should be impossible to have a VARCHAR as an AUTO_INCREMENT, regardless of the length.

Decide whether you want the PRIMARY KEY to be AUTO_INCREMENT or VARCHAR.

If you choose to have a VARCHAR, then think about what size is reasonable.

If you need to index a large VARCHAR, then see http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes for the options you have.

How can I modify the size of Primary Key column of Varchar in a MariaDB table?

3 Answers3