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.
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 one thing to note is, that 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.