CODEWITHSUNDEEP
sql-server
Allan F
Allan F

Reputation: 2288

Alter Table add column and check constraint in one script - getting Invalid column name on constraint

I'm wanting to run this script as part of another bigger script.

I'm not wanting to add GO statements as the defined variables do not carry past the GO.

As individual statements they work, but when run as a whole, I get error:

Msg 207, Level 16, State 1, Line 4 Invalid column name 'ECLIPSECapable'.

ALTER TABLE [dbo].[Providers]
ADD [ECLIPSECapable] [char](1) NULL;

ALTER TABLE [dbo].[Providers]  WITH CHECK ADD  CONSTRAINT [ICProvidersECLIPSECapable] CHECK  (([ECLIPSECapable]='N' OR [ECLIPSECapable]='Y'))

ALTER TABLE [dbo].[Providers] CHECK CONSTRAINT [ICProvidersECLIPSECapable]

Upvotes: 1

Views: 519

Answers (2)

Martin Smith
Martin Smith

Reputation: 452988

I'd just do this in one statement so there is no need to reference the column at all outside the statement that adds it.

ALTER TABLE [dbo].[Providers]
ADD [ECLIPSECapable] [char](1) NULL,
CONSTRAINT [ICProvidersECLIPSECapable] CHECK  (([ECLIPSECapable]='N' OR [ECLIPSECapable]='Y'))

The rest of the code isn't needed "WITH CHECK is assumed for new constraints" anyway so after running that statement you end up with a check constraint that is both enabled and trusted to assure that all data in the table matches the constraint.

In theory SQL Server should be able to skip doing any examination of the rows at all as the statement ensures that all existing rows will have a value of NULL for ECLIPSECapable, and this is a metadata only change, and it is possible to determine that this constant value doesn't violate the check constraint up front - but I'm not sure if SQL Server implements this optimisation.

Upvotes: 2

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88971

Use dynamic SQL break the script into multiple batches:

exec ('
ALTER TABLE [dbo].[Providers]
ADD [ECLIPSECapable] [char](1) NULL;
');

exec ('
ALTER TABLE [dbo].[Providers]  WITH CHECK ADD  CONSTRAINT [ICProvidersECLIPSECapable] CHECK  (([ECLIPSECapable]=''N'' OR [ECLIPSECapable]=''Y''))
');

exec ('
ALTER TABLE [dbo].[Providers] CHECK CONSTRAINT [ICProvidersECLIPSECapable]
');

Upvotes: 2

Related Questions