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!
As far as I'm aware there are two possible circumstances that might cause this error.
Attempting to add a default constraint to:
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 |
+------------------------------------+--------------+