how to make default null in oracle

Question

create table patient (
    p_code number(5) primary key,
    p_name varchar2(50) not null,
    DOB date(15) not null,
    p_phone number(30)   default null,
    st varchar2(20) not null,
    city varchar2(15) not null,
    state varchar2(15)   default null,
    zip_code number(10) not null,
    w_code number(5) references ward (w_code)  
)

it gives me ORA-00907: missing right parenthesis

by default its null, you don't need to say default null.

SMA
– SMA

2014-12-27 13:48:12 +00:00
Commented Dec 27, 2014 at 13:48 — SMA
– SMA, Commented Dec 27, 2014 at 13:48

Gordon Linoff · Accepted Answer · 2014-12-27 13:51:49Z

1

In all databases, the default value for a column is NULL when you leave out not null. So, you can write:

create table patient (
    p_code number(5) not null primary key,
    p_name varchar2(50) not null,
    DOB date not null,
    p_phone number(30),
    st varchar2(20) not null,
    city varchar2(15) not null,
    state varchar2(15),
    zip_code number(10) not null,
    w_code number(5) references ward (w_code)  
)

As a note. Oracle also accepts null and default null for this purpose, so these are also acceptable:

    p_phone number(30) null,
    p_phone number(30) default null,

The problem with your code was the date(15). date doesn't take a length argument.

By the way, you should be storing phone numbers and zip codes using strings and not numbers. They can have leading zeros.

answered Dec 27, 2014 at 13:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to make default null in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related