Numeric Data type SQL-CodePudding

i want to add a new column to an existing table. I want to have a numeric data type and the default value of the column must be zero. So here is what i am trying.

ALTER TABLE COUNTRY
ADD MOBILE_ACTIVE NUMERIC(1,0) NOT NULL

and i am getting the following error

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'MOBILE_ACTIVE' cannot be added to non-empty table 'COUNTRY' because it does not satisfy these conditions.

CodePudding user response：

You are trying to create a not null column, in a table that already has data.

You will need to run

ALTER TABLE COUNTRY
ADD MOBILE_ACTIVE NUMERIC(1,0) NOT NULL 
CONSTRAINT DF_MOBILE_ACTIVE DEFAULT 0 --create DEFAULT CONSTRANTI
WITH VALUES --must be used to populate the col for the existing rows

another note... why use numeric(1,0)? it just means you get a one-digit number The proper data type for a true/false value is bit, and if you need a small integer there are fitting data types like tinyint

CodePudding user response：

As @Sergey suggested in comments You should add default as in the error:

ALTER TABLE COUNTRY
ADD MOBILE_ACTIVE NUMERIC(1,0) NOT NULL DEFAULT 0

Or you should allow null as null is a non-value while 0 is a value of 0, so if you want it should have a value you should default it to 0, and if not you should allow null.

For dates you should use null, but for text and int some say never use null.

But by using default you add a constraint which will not allow you to drop the column without dropping the constraint (at least in SQL Server which I use, don't know about other), so you should add a constraint name, if you would want to be able to drop it.