How to fix Column in table is invalid for creating a default constraint

24 May,2021 by Jack Vamvas

Question: I was attempting to create a constraint and getting this error 

Msg 1752, Level 16, State 0, Line 5
Column 'col1' in table 'MyTable' is invalid for creating a default constraint.
Msg 1750, Level 16, State 0, Line 5
Could not create constraint or index. See previous errors.

  1. A computed column.
  2. A column that doesn't exist.

 

How can I fix this issue?

Answer:    As part of this error message - there are two reasons presented 

       1.A computed column.

       2. A column that doesn't exist.

Based on the table DDL - there are two ADD CONSTRAINTS below which will fail - the first is an example of trying to add a CONSTRAINT to a computed column and the second is to add a CONSTRAINT to a column which doesn't exist!

 

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

--this will fail as constraints on computed columns are not allowed
  ALTER TABLE dbo.MyConstraintTest
  ADD CONSTRAINT C0 DEFAULT '1' FOR Computed

--this will fail as the column does not exist 
ALTER TABLE [dbo].[MyConstraintTest]
  ADD CONSTRAINT [randomDateColumn] DEFAULT (GETDATE()) FOR  randomDateColumn


Check the columns you're attempting to add the CONSTRAINT. 

 

Read more on CONSTRAINTS

How to list constraints of a table (SQL Server DBA)

Script to DROP CONSTRAINT and ADD CONSTRAINT for all CHECK CONSTRAINTS


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix Column in table is invalid for creating a default constraint


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer