asked on

Drop and Create Default Constraint using Dynamic SQL

Hello Experts,

I am trying to drop a constraint on an existing table in order to add a new default value on a column.

The following code works fine but will not accept a parameter. I must be able to pass a parameter to the SQL statement.

--drop constraint
ALTER TABLE dbo.table1 DROP CONSTRAINT def_column1

--add constraint
ALTER TABLE dbo.table1
ADD CONSTRAINT def_column1
DEFAULT 'test'
FOR colum1

I think I need to use dynamic SQL in order to pass params here. I am now trying to accomplish this (drop and create a default constraint) in the SPROC below but I cannot get it to work.

CREATE PROCEDURE AddDefaultColumnSP
@Column1 varchar(10),
@TableName nvarchar(64)= 'dbo.table1',
@SQL VARCHAR(1000)= 'dynamicSQLString '
AS

--Dynamic SQL
SET @TableName = 'dbo.table1'

--Drop constraint
SET @SQL = 'ALTER TABLE' + @TableName + 'DROP CONSTRAINT' + 'def_Column1'

--add constraint
SET @SQL = 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'

EXEC(@SQL)

GO

Can anyone see what I am doing wrong here??

Thanks

D B

If this is the actual code, for one thing, you are never dropping the constraint. You use
SET @SQL = :ALTER TABLE... drop contraint,
then turn around and
SET @SQL = 'ALTER TABLE... add constraint.
I think you want your second SET to be SET @SQL = @SQL + 'ALTER TABLE... add constrint.

Secondly, there are no spaces between parts of the SET, so the first one would translate to:
ALTER TABLEdbo.table1DROP CONSTRAINTdef_column1
same for the second.

Jai S

there seems to be a typo in your SP code...
you are not leaving any SPACE before your single quotes when createing the SQL
like
SET @SQL = 'ALTER TABLE' + @TableName + 'DROP CONSTRAINT' + 'def_Column1'
it has to be
SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + 'def_Column1'

D B

jaiganeshsrinivasan: I thought that is what I said?

John Porter

ASKER

Sorry for my "greeness" here..

OK:
I am able to pass in a param for @Column1 to set the default value for column1 like this:
--this works
SET @SQL = 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD DEFAULT '''+ @Column1 + ''' FOR column1' -

But If I try to add a Constraint first like this, passing in a value for the param:
SET @SQL = @SQL + 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'
--this does not work with these error msgs :
--Line 1: Incorrect syntax near 'TABLEdbo'.
--Unclosed quotation mark before the character string ' FOR Column1'.

Thanks for your patience :)

frankytee

i think you would have to execute them seperately
--Drop constraint
SET @SQL = 'ALTER TABLE' + @TableName + 'DROP CONSTRAINT' + 'def_Column1'
EXEC(@SQL)

--now add constraint
SET @SQL = 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'

EXEC(@SQL)

-------------------------------------------------------------
building a string with several ALTER table statements may not be valid (for constraints)
you can try seperating them with commas (like when you are adding fields etc) but i dont think it would work for constraints.
but you can try below:

SET @SQL = 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD DEFAULT '''+ @Column1 + ''' FOR column1' -
--note remove ALTER TABLE for the second constraings
SET @SQL = @SQL + ', ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT'
EXEC(@SQL)

let me know how it goes

frankytee

typo carried over from your original post, there should be spaces between the arguments etc

--Drop constraint
SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + ' def_Column1 '
EXEC(@SQL)

--now add constraint
SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT ' + @Column1 + ' FOR Column1 '

etc
...

or if attempting to exec in one statement

SET @SQL = 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD DEFAULT '+ @Column1 + ' FOR column1 '
--note remove ALTER TABLE for the second constraints
SET @SQL = @SQL + ', ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT '
EXEC(@SQL)

D B

What are the values you are passing for
@TableName and @Column1

After the statement:
SET @SQL = @SQL + 'ALTER TABLE' + @TableName + 'ADD CONSTRAINT' + 'def_Column1' + 'DEFAULT' + @Column1 + ''' FOR Column1'

add:
PRINT @SQL

and copy and paste the result back into a reply.

John Porter

ASKER

Hi dbbishop - Thanks for the replies.

1. First I ran SPROC below to Add a constraint only

CREATE PROCEDURE AddDefaultColumn1SP
@Column1 varchar(10) = 'Test',
@TableName nvarchar(64)= 'dbo.table1',
@SQL VARCHAR(1000)= 'dynamicSQLString '
AS

-- this runs without error but it does not add a default value to Column1 in table1
SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT ' + @Column1 + ' FOR Column1 '

PRINT @SQL
GO

Result:

Print @SQL output:
ALTER TABLE dbo.table1 ADD CONSTRAINT def_Column1 DEFAULT Test FOR Column1

SPROC runs without error but doesnt appear to alter Column1 in table1. Test was not added as a default value but <database default> was added for Collation (Not sure what this means).

2. When I run the Drop Constraint statement, it fails with the message along the lines of No Constraint to Drop

It seems like step one is not adding the constraint def_Column1 and is not adding the default value of Test.

Thanks!

D B

SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT (''' + @Column1 + ''') FOR Column1'

John Porter

ASKER

OK - I ran this line:
SET @SQL = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + ' def_Column1 ' + ' DEFAULT (''' + @Column1 + ''') FOR Column1'

Output from PRINT looks like this:
ALTER TABLE dbo.table1 ADD CONSTRAINT def_Column1 DEFAULT ('Test') FOR Column1

Default value is not added to Column1. Also, I can run the SPROC multiple times without "Already a CONSTRAINT bound" error.

Thanks

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Porter

ASKER

I have it working now. I was missing EXEC(@SQL) after the ADD CONSTRAINT string.

Thanks for spelling it out for me!!

D B

Glad to help. Good luck!