'invalid for creating a default constraint' error when trying to add a constraint to an existing table

TechCrave Published at Dev

TechCrave

I want to add a default constraint using ALTER TABLE in SQL Server, but I received the below error message instead.

Column 'StartDate' in table 'Register' is invalid for creating a default constraint.

I know that I can declare a constraint when I create a table, but that isn't the situation I am in. I hope someone can help me ;)

Here is my alter statement:

ALTER TABLE [dbo].[Register]
  ADD CONSTRAINT [Register_StartDate] DEFAULT (GETDATE()) FOR StartDate

And this is my create table script:

CREATE TABLE [dbo].[Register]
  (
     [ID]        [INT] IDENTITY(1, 1) NOT NULL,
     /* ....*/
     [StartDate] [DATETIME] NULL
  )

Edited: Fixed: I forgot that the [StartDate] field doesn't even exist in the table. My bad!

Martin Smith

As far as I'm aware there are two possible circumstances that might cause this error.

Attempting to add a default constraint to:

A computed column.
A column that doesn't exist at all!

For the table definition

CREATE TABLE dbo.Register
  (
     ID INT IDENTITY(1, 1) NOT NULL,
     Computed AS 'Foo'
  )

Both of the following statements fail with error 1752.

ALTER TABLE dbo.Register
  ADD CONSTRAINT C1 DEFAULT 'X' FOR Computed

ALTER TABLE [dbo].[Register]
  ADD CONSTRAINT [Register_StartDate] DEFAULT (GETDATE()) FOR StartDate

There are various other conditions on which it is not permissible to add a default constraint to a column but these all have their own unique error numbers and messages.

+------------------------------------+--------------+
|               Reason               | Error number |
+------------------------------------+--------------+
| Column is IDENTITY                 |         1754 |
| Column is timestamp/rowversion     |         1755 |
| Sparse Column                      |         1791 |
| Default constraint already present |         1781 |
+------------------------------------+--------------+

Collected from the Internet

Please contact [email protected] to delete if infringement.