Home Dashboard Directory Help
Search

Error when altering a table to add a Not Null column with a default using a case statement by NikkiJ


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 869684
Opened: 5/8/2014 10:56:34 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

An error occurs when running this alter statement on a SQL 2012 Developer or Enterprise edition database:

ALTER TABLE dbo.NJ_TEST ADD user_id sysname NOT NULL
            CONSTRAINT DF__NJ_TEST__user_id
                DEFAULT (CASE WHEN (SYSTEM_USER LIKE '%_SOMETEXT' OR SYSTEM_USER LIKE '%_OTHERTEXT')
                         THEN SUBSTRING(SYSTEM_USER, 1, LEN(SYSTEM_USER)-5)
                         ELSE SYSTEM_USER
                         END)


The error given is "Msg 1750, Level 16, State 0, Line 10 Could not create constraint. See previous errors.", but no other errors are thrown. This statement works properly on any edition of SQL Server 2005 or 2008, and 2012 Standard edition.
Details
Sign in to post a comment.
Sign in to post a workaround.