Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Similarities and Differences Between LEN and DATALENGTH Functions
Error Messages
Home > SQL Server Error Messages > Msg 4901 - 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 '<Column Name>' cannot be added to non-empty table '<Table Name>' because it does not satisfy these conditions.
SQL Server Error Messages - Msg 4901 - 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 '<Column Name>' cannot be added to non-empty table '<Table Name>' because it does not satisfy these conditions.

Error Message

Server: Msg 4901, Level 16, State 1, Line 1
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 '<Column Name>' cannot be added to non-empty 
table '<Table Name>' because it does not satisfy these 
conditions.

Causes

The ALTER TABLE statement modifies a table definition by altering, adding or dropping columns and constraints, reassigning partitions or disabling or enabling constraints and triggers.  To add a new column to an existing table, the ALTER TABLE ADD <Column Definition> syntax is used.

A new column can be added to an existing table even if the table already contains data. If the table being modified already contains data and the new column being added is defined not to allow NULL values, then a DEFAULT value must be defined for the new column. Otherwise, this error message will be raised.

To illustrate, here’s a table of students ([dbo].[Students]) that contains the first name and last name of students in the school.

CREATE TABLE [dbo].[Students] (
    [StudentID]          INT NOT NULL IDENTITY(1, 1),
    [FirstName]          VARCHAR(50) NOT NULL,
    [LastName]           VARCHAR(50)
)
GO

INSERT INTO [dbo].[Students] ( [FirstName], [LastName] )
VALUES ( 'Mark', 'Zuckerberg' )

INSERT INTO [dbo].[Students] ( [FirstName], [LastName] )
VALUES ( 'Bill', 'Gates' )

INSERT INTO [dbo].[Students] ( [FirstName], [LastName] )
VALUES ( 'Larry', 'Page' )

INSERT INTO [dbo].[Students] ( [FirstName], [LastName] )
VALUES ( 'Larry', 'Ellison' )

INSERT INTO [dbo].[Students] ( [FirstName], [LastName] )
VALUES ( 'Marissa', 'Mayer' )
GO

StudentID  FirstName   LastName
---------- ----------- ------------
1          Mark        Zuckerberg
2          Bill        Gates
3          Larry       Page
4          Larry       Ellison
5          Marissa     Mayer

The table needs to be modified to include the gender of each student. Each student record must have a value on this column. Given this, the following ALTER TABLE statement is issued to add the [Gender] column which will not allow NULL values.

ALTER TABLE [dbo].[Students]
ADD [Gender] CHAR(1) NOT NULL
GO

But since the table already contains data, the following error message is raised by SQL Server:

Msg 4901, Level 16, State 1, Line 2
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 'Gender' cannot
be added to non-empty table 'Students' because it does not satisfy these conditions.

Similarly, the [dbo].[Students] table needs to be modified to include the email address of each student as one of their contact information. The email address is required as well, similar to the [Gender] column, but the data is going to come from another column called [dbo].[Applicants], as illustrated in the following script:

CREATE TABLE [dbo].[Applicants] (
    [ApplicantID]      INT NOT NULL IDENTITY(1, 1),
    [FirstName]        VARCHAR(50) NOT NULL,
    [LastName]         VARCHAR(50) NOT NULL,
    [Email]            VARCHAR(100) NOT NULL,
    [Phone]            VARCHAR(30)
)
GO

INSERT INTO [dbo].[Applicants] ( [FirstName], [LastName], [Email] )
VALUES ( 'Mark', 'Zuckerberg', 'mark.zuckerberg@facebook.com' )

INSERT INTO [dbo].[Applicants] ( [FirstName], [LastName], [Email] )
VALUES ( 'Bill', 'Gates', 'bill.gates@microsoft.com' )

INSERT INTO [dbo].[Applicants] ( [FirstName], [LastName], [Email] )
VALUES ( 'Larry', 'Page', 'larry.page@google.com' )

INSERT INTO [dbo].[Applicants] ( [FirstName], [LastName], [Email] )
VALUES ( 'Larry', 'Ellison', 'larry.ellison@oracle.com' )

INSERT INTO [dbo].[Applicants] ( [FirstName], [LastName], [Email] )
VALUES ( 'Marissa', 'Mayer', 'marissa.mayer@yahoo.com' )
GO

To add the [Email] column to the [dbo].[Students] table, the following ALTER TABLE command is executed:

ALTER TABLE [dbo].[Students]
ADD [Email] VARCHAR(100) NOT NULL 
GO

For the same reason with the [Gender] column, since the table already has data and the column being added does not allow NULL values, the following error is raised as well:

Msg 4901, Level 16, State 1, Line 2
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 'Email' cannot 
be added to non-empty table 'Students' because it does not satisfy these conditions.

Solution / Work Around:

In the first scenario of adding the [Gender] column, there are two ways of overcoming this error message. The first option is to allow NULL values to the column, as can be seen in the following ALTER TABLE statement:

ALTER TABLE [dbo].[Students]
ADD [Gender] CHAR(1) NULL
GO

The issue with this option is that it is not in accordance with the business rules set for the table of not allowing NULL values to the column. An alternative option is still not allow NULL values to the column but set a default value to the column as the error message suggests. Since the gender of each student is not yet determined during the adding of the column to the table, a value of “U” for undetermined can be used as the default value for the column. Here’s how the ALTER TABLE statement will look like when adding the column with a default value:

ALTER TABLE [dbo].[Students]
ADD [Gender] CHAR(1) NOT NULL DEFAULT('U')
GO

Aside from a value of “U”, a blank space can also be used as the default value:

ALTER TABLE [dbo].[Students]
ADD [Gender] CHAR(1) NOT NULL DEFAULT(' ')
GO

In the case of adding the email address, a default value cannot be used since the values are going to come from another table ([dbo].[Applicants]). Although a blank space can be used as the default value similar to the last option described for adding the Gender column earlier, a different approach will be used for adding the email address.

First, the email address column will be added and defined to allow NULL values:

ALTER TABLE [dbo].[Students]
ADD [Email] VARCHAR(100) NULL 
GO

After having the [Email] column added, this is now populated with the email address from the second table, the [dbo].[Applicants] table:

UPDATE S
SET [Email] = A.[Email]
FROM [dbo].[Students] S INNER JOIN [dbo].[Applicants] A
  ON S.[FirstName] = A.[FirstName] AND
     S.[LastName]  = A.[LastName]
GO

Now that all students have a value in the email address column, the column can now be changed to not allow NULL values. This is done using the ALTER COLUMN clause of the ALTER TABLE statement, as can be seen in the following script:

ALTER TABLE [dbo].[Students]
ALTER COLUMN [Email] VARCHAR(100) NOT NULL
GO

Here’s the final content of the [dbo].[Students] table with the new columns included as well as with the column values set:

StudentID  FirstName   LastName     Gender  Email
---------- ----------- ------------ ------- ------------------------------
1          Mark        Zuckerberg   U       mark.zuckerberg@facebook.com
2          Bill        Gates        U       bill.gates@microsoft.com
3          Larry       Page         U       larry.page@google.com
4          Larry       Ellison      U       larry.ellison@oracle.com
5          Marissa     Mayer        U       marissa.mayer@yahoo.com
Related Articles :