How to name constraints on computed fields in T-SQL?

Question

While adopting some new computed fields in our database, we need to be able to revert the changes and then put them back again.

The essential problem can be explained with the following script. It does not work, because there is a generated constraint that should be dropped before dropping the column Field1:

CREATE TABLE Table1 
(
    Field1 DECIMAL(19, 2) NOT NULL DEFAULT(0.0),
    Field2 AS Field1
)
GO

ALTER TABLE Table1
DROP COLUMN Field2, Field1
GO

DROP TABLE Table1
GO

SSMS shows:

Msg 5074, Level 16, State 1, Line 10
The object 'DF__Table1__Field1__5AC46587' is dependent on column 'Field1'.

Msg 4922, Level 16, State 9, Line 10
ALTER TABLE DROP COLUMN Field1 failed because one or more objects access this column.

The code works, if the constraint DF__Table1__Field1__5AC46587 is dropped before dropping the column Field1:

ALTER TABLE Table1
DROP CONSTRAINT DF__Table1__Field1__5AC46587
GO

The problem is that the name of the constraint is different each time when Field2 is created.

Is it possible to have a constant name for this constraint or somehow find it easily in the script?

You can add the constraint as a separate command and the name it. It you script your table using SSMS it will do it exactly like that. — Dale K, Dec 16 '19 at 06:51
https://stackoverflow.com/questions/3770015/can-i-create-a-named-default-constraint-in-an-add-column-statement-in-sql-server — Max Zolotenko, Dec 16 '19 at 06:51
@DaleK: I am afraid not. As far as I understand, the constraint is tightly connected with the creation of the column as a computed column. It is not possible to create it with a separate command afterwards (would not make any sense?) — masa, Dec 16 '19 at 07:38
There is no constraint on your computed column? The constraint is on Field1 which is a real field, the computed column is Field2. So the issue isn't with your computed column, the error refers to Field1? Therefore you can name the constraint during your create or after, whichever you like. — Dale K, Dec 16 '19 at 07:58
@DaleK: I was wrong, the proposed answer answers this question as well. — masa, Dec 16 '19 at 07:59

marc_s · Accepted Answer · 2019-12-16T07:50:32.533

Just make sure to name the constraint when creating it!

CREATE TABLE Table1 
(
    Field1 DECIMAL(19, 2) NOT NULL 
        CONSTRAINT DF_Table1_Field1 DEFAULT(0.0),   -- use the "CONSTRAINT" clause and a fixed name
    Field2 AS Field1
)
GO

It's a really good practice to always define explicit names for any of your constraints - so that you know what you're doing, and you have the ability to drop or disable those constraints (since they have a well-defined name).

Update: if you want to add the constraint later on, after the table has been created, you can also use:

CREATE TABLE dbo.Table1 
(
    Field1 DECIMAL(19, 2) NOT NULL,
    Field2 AS Field1
)
GO

// then sometime later
ALTER TABLE dbo.Table1
ADD CONSTRAINT DF_Table1_Field1 DEFAULT(0.0) FOR Field;

After some trials, I thought the issue was somehow related with `Field2`, but it was not. — masa, Dec 16 '19 at 07:58

How to name constraints on computed fields in T-SQL?

1 Answers1