how to remove default constraint from column in Db2

Question

I had a table STUDENT_TB, which had column STUDENT_ID, NAME, AGE. I added a column with a following command :-

alter table STUDENT_TB add DOB TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP

as for the DOB column i didn't wanted it to be null. Now i need to remove that default constraint.

I tried searching but not got any success.

Regards.

Please clarify if you are attempting to remove the `NOT NULL` constraint, or the `DEFAULT CURRENT TIMESTAMP` clause. Please note that DB2 (and I believe all DBMS's) will _always_ place some sort of default, even if that is `null` - the initial wording of the question makes it appear that you want no default to be placed. You don't specify the flavor, but likely the inherent default (what happens if `default` is not specified) will by the current timestamp if the column is not nullable (`null` if it is). — Clockwork-Muse, Oct 04 '11 at 18:29
i need to remove the default constraint.. anyways i tried one thing and that worked.. will post soon.. — M.J., Oct 05 '11 at 06:17
Okay, looks like I was wrong: specifying `NOT NULL` but _not_ `DEFAULT` causes (at least on the iSeries version) the system to throw an error on insert (if that column is not given a value). Specifying _neither_ is an implicit `DEFAULT NULL`. — Clockwork-Muse, Oct 05 '11 at 16:05

score 7 · Accepted Answer · answered Oct 05 '11 at 06:19

7

I tried with this and it worked properly

alter table STUDENT_TB alter DOB drop DEFAULT

answered Oct 05 '11 at 06:19

M.J.

score 2 · Answer 2 · answered Oct 04 '11 at 13:22

2

ALTER TABLE STUDENT_TB ALTER COLUMN DOB DROP NOT NULL

answered Oct 04 '11 at 13:22

egbokul

2 Answers2