|
|
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
|