CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Jan Van Looveren
Jan Van Looveren

Reputation: 928

Add pre-defined default to column in T-SQL

I try to test something and I get the issue that I cannot bind the default to the column.

My goal is to alter the column and attach the pre-defined default True to that column.

What am I doing wrong? 

CREATE default [dbo].[True] as (1)
GO

CREATE TABLE dbo.aaa ( column_a INT, column_b char(1)) ;
GO

INSERT INTO dbo.aaa (column_a)VALUES ( 7 ) ;
GO

ALTER TABLE dbo.aaa ADD CONSTRAINT DF_col_b DEFAULT [dbo].[True] FOR column_b ;
GO

INSERT INTO dbo.aaa (column_a) VALUES ( 10 ) ;
GO

SELECT * FROM dbo.aaa ;
GO

DROP TABLE dbo.aaa ;
GO

In this example I get the error on the ALTER TABLE line

Msg 128, Level 15, State 1, Line 1

The name "dbo.True" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Upvotes: 5

Views: 1728

Answers (2)

Valentino Vranken
Valentino Vranken

Reputation: 5815

If you want your column to default to "1", why don't you try this:

ALTER TABLE dbo.aaa ADD CONSTRAINT DF_col_b DEFAULT '1' FOR column_b ;

Your default is called True. If the only possibilities are True or False, then you should probably change the type from char(1) to bit. And the alter statement would become:

ALTER TABLE dbo.aaa ADD CONSTRAINT DF_col_b DEFAULT 1 FOR column_b ;

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

There is no syntax for binding pre-defined defaults. You need to use stored procedure:

sp_bindefault 'True', 'dbo.aaa.column_b'

Upvotes: 0

Related Questions