Unable to modify column in MySQL table

Question

I have a requirement wherein I need to change the table structure as per the production environment on a lower environment. The table has a multi-column PRIMARY KEY as (md_biobjectid,projectid,md_mapid), I want to modify column 'md_mapid' to varchar(50) DEFAULT NULL from 'md_mapid' varchar(50) NOT NULL.

When I am running the query : alter table table_name modify column md_mapid varchar(50) DEFAULT NULL; it doesn't run and I am getting following error : Error Code: 1171. All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead.

Other columns structure on both the environment is : 'md_biobjectid' varchar(50) NOT NULL DEFAULT ''

'projectid' varchar(50) NOT NULL DEFAULT ''

MySQL version : 5.7.21-log.

Its stating exact reason. You can make a primary key using a single column. and these three columns you can make as `Unique` — Kaushik, Dec 27 '18 at 09:47
because what you're try to do is violating the rule for each primary key constraints. — Barbaros Özhan, Dec 27 '18 at 09:47

score 0 · Answer 1 · answered Dec 27 '18 at 09:47

0

you need to isse without DEFAULT NULL postfix. md_mapid column is part of composite primary key and cannot be set to null.

alter table table_name modify column md_mapid varchar(50)

answered Dec 27 '18 at 09:47

Derviş Kayımbaşıoğlu

28,492
4
50
72

Unable to modify column in MySQL table

1 Answers1