Add a column not null without default

Question

When I try to add this column:

alter table APL ADD CODE NUMERIC(2) NOT NULL

I get this error:

ALTER TABLE 'APL 'failed. Default clause is required in order to add non-NULL column 'CODE'.

I know I can add a default and it will work, but I DONT WANT A DEFAULT VALUE in this coloumn, i read and i heard that there is way that i can add the column with out a default.
anyone knows how ?

Edit:
Ill explain more about my case.
This column was created by mistake as NULL(I dont know why), and its part of primary key(luckily I have no duplicate), so I have to change it to NOT NULL , so I can assign it .
Its really an old column and I dont know what kind of default I should add, because if I add a wrong value the applications might cause problems.

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

I found this query that suits to me, it seems fine for me. Anyone tried this before ?
I add the column as NULL , then I modify it to not null.

alter table TEST_EMPLOYEE ADD COMP_CODE NUMERIC(4) NULL
alter table TEST_EMPLOYEE MODIFY COMP_CODE NUMERIC(4) NOT NULL

edited Jun 20 '20 at 09:12

Community

1
1

answered May 31 '13 at 07:18

Moudiz

7,211
22
78
156

but this method makes NULL value exist on a column that is NOT NULL – DevZer0 May 31 '13 at 07:55
@devzer0 what do you mean ? If i try to insert a NULL value in this column I will get an error that this column does not allow null values. – Moudiz May 31 '13 at 08:01
1

@moudiz you have to consider the existing rows that have null values in the COMP_CODE column? If this were permitted then they would be breaking the condition. – AdamH May 31 '13 at 09:02
@AdamH this column should be part of primary key, thats why I need it as NOT NULL , and I am using powerbuilder and there is cases if the column have DEFAULT VALUE , the aplication will cause problems .This column shoUld'nt be null – Moudiz May 31 '13 at 10:05

score 2 · Answer 2 · answered May 31 '13 at 06:53

If you think about your requirement is impossible.

You want to specify "NOT NULL" which tells the database that every row must have a value for this column, which the database is quite happy to do providing you give it a default value for the columns in the existing rows.

But you don't want to supply a default, so how is the database supposed to make the column "NOT NULL" in the existing rows?

Add a column not null without default

2 Answers2