|
|
Error Message
Server: Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Causes
Each column in a table row must contain a value, even if that value is NULL. There may be cases when data needs to be loaded into a table but the value for a column is unknown or the value does not exist yet. If the column allows for NULL values, then the loaded data will have a NULL value for those columns. Nullable columns may sometimes not be desirable and this is where DEFAULT definition for a column comes in handy.
|
A DEFAULT definition can be created as part of the table definition when the table is created. If a table already exists, DEFAULT definition can be added to it. Each column in a table can contain only one DEFAULT definition. The default value must be compatible with the data type of the column to which the DEFAULT definition applies.
DEFAULT definitions cannot be created on columns defined with the following:
- Timestamp data type
- IDENTITY or ROWGUIDCOL property
- Existing DEFAULT definition or DEFAULT object
When a DEFAULT definition is added to an existing column in a table, by default, SQL Server applies the new default only to new rows of data that are inserted to the table. Existing data that was inserted by using the previous DEFAULT definition is unaffected. However, when a new column is added to an existing table, it can be specified that the default value, as specified by the DEFAULT definition, be inserted instead of a NULL value into the newly added column for the existing rows in the table.
When a DEFAULT definition is deleted, a NULL value is inserted instead of the default value when no value is inserted into the column for new rows. However, no changes are made to the existing data in the table.
As mentioned earlier, a column in a table can only contain one DEFAULT definition. If a column already has a DEFAULT definition, adding another one using the ADD CONSTRAINT construct of the ALTER TABLE statement will generate this error message as can be seen in the following script:
CREATE TABLE [dbo].[Client] (
[ClientID] INT NOT NULL IDENTITY(1, 1),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[IsActive] BIT DEFAULT(1)
)
GO
ALTER TABLE [dbo].[Client]
ADD CONSTRAINT DF_IsActive DEFAULT 0 FOR [IsActive]
GO
Msg 1781, Level 16, State 1, Line 2
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Solution / Work Around:
Since a column in a table can only have one DEFAULT definition, this error message can be avoided by not adding a new DEFAULT definition to the column. If the purpose is to change the default value of a column instead of adding a new DEFAULT definition, then the existing DEFAULT definition needs to be dropped first before the new default value can be defined.
To drop an existing DEFAULT definition from a column, the DROP CONSTRAINT construct of the ALTER TABLE statement can be used. The name of the DEFAULT definition constraint needs to be known so that it can be dropped. If the name of the DEFAULT definition constraint is not known, it can be determined using either the [dbo].[sp_help] system stored procedure or the [dbo].[sp_helpconstraint] system stored procedure:
EXECUTE [dbo].[sp_help] 'Client'
GO
EXECUTE [dbo].[sp_helpconstraint] 'Client'
GO
The table name is passed as a parameter to either of these system stored procedures. The [dbo].[sp_help] system stored procedure reports information about a database object, a user-defined data type, or a data type supplied by SQL Server. On the other hand, the [dbo].[sp_helpconstraint] system stored procedure returns a list of all constraint types, their user-defined or system-supplied name, the columns on which the constraint have been defined, and the expression that defines the constraint.
Once the name of the DEFAULT definition constraint has been determined, it can now be dropped and the new default value can be associated to the column using the ADD CONSTRAINT construct of the ALTER TABLE statement:
ALTER TABLE [dbo].[Client]
DROP CONSTRAINT DF__Client__IsActive__69C6B1F5
GO
ALTER TABLE [dbo].[Client]
ADD CONSTRAINT DF_IsActive DEFAULT 0 FOR [IsActive]
GO
|