Add DEFAULT CONSTRAINT at the end of a CREATE TABLE

Question

How can I add a DEFAULT CONSTRAINT at the end of a CREATE TABLE?

I would like to have an independent line at the end like

CREATE TABLE myTable (
  table_id       INT NOT NULL,
  firstColumn    NVARCHAR(10),
  secondColumn   INT,
  thirdColumn    INT,
  CONSTRAINT DEF_secColumn DEFAULT 0 FOR secondColumn
)

It's not working. I get the error message

Incorrect syntax near 'for'.

May you can help me? Thanks a lot in forward!

Answer 1

You should just use default next to the field you need the default value for, e.g :

CREATE TABLE myTable (
  table_id       INT NOT NULL,
  firstColumn    NVARCHAR(10),
  secondColumn   INT DEFAULT 0,
  thirdColumn    INT
)

Or if you really want to add the constraint, you can add it later with the ALTER TABLE :

CREATE TABLE myTable (
  table_id       INT NOT NULL,
  firstColumn    NVARCHAR(10),
  secondColumn   INT,
  thirdColumn    INT
)

ALTER TABLE myTable ADD CONSTRAINT DEF_secColumn DEFAULT 0 FOR [secondColumn]

Or 3rd option as suggested by @jeroen-mostert

CREATE TABLE myTable (
  table_id       INT NOT NULL,
  firstColumn    NVARCHAR(10),
  secondColumn   INT CONSTRAINT DEF_secColumn DEFAULT 0,
  thirdColumn    INT
)