HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

SQL SERVER: Create table with named default constraint

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
createsqlwithnameddefaultconstraintservertable

Problem

I'd like to create a table with a default constraint for a field. I know I can add a constraint after the table is created but I need to create the table and the named default constraint in one single command, like this

create table table1(field1 varchar(25), constraint df_table1_field1 default('a') for field1)

Unfortunately that doesn't work.

Any suggestions?

Solution

Use this syntax:

CREATE TABLE table1
(
    field1 varchar(25) CONSTRAINT [df_table1_field1] DEFAULT('a')
)


Have a look at MS Docs about Specify Default Values for Columns, specifically this secion: Named CONSTRAINT (T-SQL)

CREATE TABLE dbo.doc_exz (
      column_a INT,
      column_b INT CONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50);

Code Snippets

CREATE TABLE table1
(
    field1 varchar(25) CONSTRAINT [df_table1_field1] DEFAULT('a')
)
CREATE TABLE dbo.doc_exz (
      column_a INT,
      column_b INT CONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50);

Context

StackExchange Database Administrators Q#301758, answer score: 12

Revisions (0)

No revisions yet.