'invalid for creating a default constraint' error when trying to add a constraint to an existing table
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 |
+------------------------------------+--------------+