How to alter the default value set to a column in a table in SQL?

Question

How can you alter the default value set to a column in a table in SQL.

I got an error from:

ALTER TABLE  tablename.tab ALTER COLUMN mess1 DEFAULT ('hi')

What was the correct query?

Answer 1

Normally, the syntax is a variant of:

ALTER TABLE jankhana.jankh MODIFY (mess1 CHAR(10) NOT NULL DEFAULT 'hi');

Technically, the parentheses around the column specification are optional when there's just one column; if there are several, they are mandatory.

The details could vary by DBMS - DDL statements tend to be the most variable.

Answer 2

I would name your constraints. To change an existing one...

ALTER TABLE tablename.tab
        DROP CONSTRAINT ....  --you have a system generated name. Well done.
ALTER TABLE tablename.tab
        ADD CONSTRAINT DF_tablename_mess1 DEFAULT 'hi' FOR mess1