what's the problem with query

[manojmalviya1982]

CREATE TABLE "calc_modes" (
"template" VARCHAR2(20) NOT NULL,
"code" VARCHAR2(20) NOT NULL,
"name" VARCHAR2(40) NULL,
"type" NUMBER(4) NULL DEFAULT (0)
)

what is the proble with this query

[mivey4]

CREATE TABLE "calc_modes" (
"template" VARCHAR2(20) NOT NULL,
"code" VARCHAR2(20) NOT NULL,
"name" VARCHAR2(40) NULL,
"type" NUMBER(4) NULL DEFAULT (0)
)

what is the proble with this query

Remove the double quotes and if the column TYPE is to be assigned a default value, then how can it be null?

It's either allowed NULL values or assigned a default value but not both.

CREATE TABLE calc_modes (
template VARCHAR2(20) NOT NULL,
code VARCHAR2(20) NOT NULL,
name VARCHAR2(40) NULL,
type NUMBER(4) DEFAULT 0
);

Good Luck.

[Dave44]

Remove the double quotes and if the column TYPE is to be assigned a default value, then how can it be null?

It's either allowed NULL values or assigned a default value but not both.

CREATE TABLE calc_modes (
template VARCHAR2(20) NOT NULL,
code VARCHAR2(20) NOT NULL,
name VARCHAR2(40) NULL,
type NUMBER(4) DEFAULT 0
);

Good Luck.

mivey4,

giving a table column a default value has nothing to do with whether or not it can be null. And there may be perfectly valid reasons for giving a nullable field a default value and visa versa.

[138]dave@ORADB> create table a (
  2  col1  number  default 1 not null,
  3  col2  number  default 2 null );
 
Table created.
 

what you cannot do is this:

[138]dave@ORADB> insert into a values (1,null);
 
1 row created.
 
[138]dave@ORADB> alter table a modify col2 not null;
alter table a modify col2 not null
*
ERROR at line 1:
ORA-02296: cannot enable (DAVE.) - null values found
 

[mivey4]

mivey4,

giving a table column a default value has nothing to do with whether or not it can be null. And there may be perfectly valid reasons for giving a nullable field a default value and visa versa.

[138]dave@ORADB> create table a (
  2  col1  number  default 1 not null,
  3  col2  number  default 2 null );
 
Table created.
 

what you cannot do is this:

[138]dave@ORADB> insert into a values (1,null);
 
1 row created.
 
[138]dave@ORADB> alter table a modify col2 not null;
alter table a modify col2 not null
*
ERROR at line 1:
ORA-02296: cannot enable (DAVE.) - null values found
 

Sorry about that,

You are absolutely correct, Dave. I didn't devote a lot of thought on that until after I'd posted the response. Thanks for the correction.

mivey4