while adding multiple columns in table giving error in sql ser

Question

getting error while adding multiple coloumns in a table

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 'Country' cannot be added to non-empty table 'tbl_alerts' because it does not satisfy these conditions.

Here is my alter table statement

ALTER TABLE tbl_alerts
         ADD 
        Country [varchar](50) NOT NULL,
        StateName [varchar](50) NOT NULL,
        City [varchar](50) NOT NULL, 
        MacAddress [varchar](50) NOT NULL,
        OSDetails [varchar](50) NOT NULL,
        MachineName [varchar](50) NOT NULL,
        Browser [varchar](50) NOT NULL

When adding a non-nullable column, you should specify a default value. — Felix Pamittan, May 28 '15 at 06:52
The problem is that *already existing rows* of the table would end up having a new `NOT NULL` column without any values in it. — Giorgos Betsos, May 28 '15 at 06:55
Why don't people ever read the error message? It's there so that you could know what went wrong! it's plain English, so other then translating it to your native language, I don't know how can someone answer your question better then the error message have already did. — Zohar Peled, May 28 '15 at 07:00

score 2 · Answer 1 · answered May 28 '15 at 07:00

The problem is your table already contains rows and you are attempting to add not null columns without a default value. In this case, the database has no way of knowing what values to fill those columns with, and since they must have values (they're defined as not null), it will fail.

One way to solve this is to add default values to the column specifications:

ALTER TABLE tbl_alerts
    ADD 
    Country [varchar](50) NOT NULL DEFAULT 'USA',
    StateName [varchar](50) NOT NULL DEFAULT 'NY',
    City [varchar](50) NOT NULL DEFAULT 'NY', 
    MacAddress [varchar](50) NOT NULL DEFAULT '68:f7:28:40:e8:b4',
    OSDetails [varchar](50) NOT NULL DEFAULT 'Fedora 21',
    MachineName [varchar](50) NOT NULL DEFAULT 'MyComputer',
    Browser [varchar](50) NOT NULL DEFAULT 'Firefox'

while adding multiple columns in table giving error in sql ser

1 Answers1