Add pre-defined default to column in T-SQL

Question

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.

Answer 1

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 ;