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

alter-table, constraints, sql, sql-server

Solution

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

Problem

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!

Original source