TechCrave TechCrave - 1 year ago 116
SQL Question

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

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!

Answer Source

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

Attempting to add a default constraint to:

  1. A computed column.
  2. 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 |
+------------------------------------+--------------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download