How to Change Default Null to NOT NULL in MySQL

Question

I have the following column in table room Now I want to change the definition of column property_id from NULL to NOT NULL How can I do this following Query is not working

ALTER TABLE `room` CHANGE `property_id` `property_id` bigint(20)  NOT NULL;

Actual definition of Column is :

 `property_id` bigint(20) DEFAULT NULL,

do you have any rows contain NULL before your change the table definition? — LONG
– LONG, Commented Jul 13, 2017 at 12:32
@SFAH You don't need to delete them, per se. But they cannot be NULL when you change the column definition. What you need to do with your data depends on your data and how you use it. — Siyual
– Siyual, Commented Jul 13, 2017 at 12:34
@SFAH, then you cannot do that, you have to treat those NULL first otherwise if will conflict the column properties — LONG
– LONG, Commented Jul 13, 2017 at 12:34

Barmar · Accepted Answer · 2017-07-13 13:04:30Z

3

In mysql you can achieve this as,

ALTER TABLE room MODIFY property_id bigint(20) not null;

edited Jul 13, 2017 at 13:04

Barmar

789k57 gold badges555 silver badges669 bronze badges

answered Jul 13, 2017 at 12:35

Sk. Irfan

3093 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sk. Irfan Over a year ago

In other dbms, such as oracle, it will not allow you alter a nonempty column, but mysql automatically casts things around for you when you alter columns

Sk. Irfan Over a year ago

@SFAH, But you kept two 'property_id' s in ur query. Check once.

SFAH · Accepted Answer · 2017-07-14 07:27:59Z

1

My Query was perfectly fine , The only mistake is that there are some rows that contains null record so that why it is creating an issue

answered Jul 14, 2017 at 7:27

SFAH

64418 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to Change Default Null to NOT NULL in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related