CODEWITHSUNDEEP
mysql
vijaykumar
vijaykumar

Reputation: 4806

How to Add Not NULL constrain for exisitng table column?

I have table with data Now I'm trying to alter my table

I tried both ways but both show same error

ALTER TABLE sample MODIFY latitude  NUMERIC(10, 7) NOT NULL DEFAULT 0;

ALTER TABLE sample CHANGE latitude latitude NUMERIC(10, 7) NOT NULL DEFAULT 0;

#1138 - Invalid use of NULL value

I think I have some null data for latitude column need Delete/modify those column first then only it will work or any other ways please suggest!

Upvotes: 1

Views: 51

Answers (1)

Leandro Carracedo
Leandro Carracedo

Reputation: 7345

It looks like you still have null values in that column, first update them.

UPDATE sample SET latitude = 0 WHERE latitude IS NULL;
and then
ALTER TABLE sample MODIFY latitude NUMERIC(10, 7) NOT NULL DEFAULT 0;

Upvotes: 2

Related Questions