Adding a column whose value is not null by default .

Question

I have to add a column whose default value is not null by default to the table after particular column using Alter table.

ALTER TABLE tblechecklistrevision ADD COLUMN IWorkFlowOrder INT(10) DEFAULT NOT NULL AFTER fState;

When I Execute the query I will get the below error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL AFTER fState' at line 1

Answer 1

You should remove DEFAULT:

ALTER TABLE tblechecklistrevision 
    ADD COLUMN IWorkFlowOrder INT(10) NOT NULL AFTER fState;

DEFAULT is for setting initial value to new rows where a value for that column isn't specified, when you write ...INT(10) NOT NULL what you mean is actually that that column can never contain a NULL, not only at initialization time.

Answer 2

If you want the default value not to equal NULL (example 0) you can do:

ALTER TABLE tblechecklistrevision 
    ADD COLUMN IWorkFlowOrder INT(10) NOT NULL DEFAULT 0 AFTER fState