How to set a default value when inserting null

Question

I'm trying to set a default value for my customer table in Oracle.

This is my coding

CREATE TABLE CUSTOMER
(
CUST_ID     VARCHAR(10),
CUST_NAME   VARCHAR(20)   NOT NULL UNIQUE,
CUST_DOB    DATE          NULL,               
CUST_STATE  VARCHAR(20)   DEFAULT 'NOT STATED',
PRIMARY KEY(CUST_ID)
);

This is my insertion...

INSERT INTO CUSTOMER VALUES ('C001','Murphy','1/30/1989','Melaka');
INSERT INTO CUSTOMER VALUES ('C002','Cooper','4/20/1993','Selangor');
INSERT INTO CUSTOMER VALUES ('C003','Richard','','Perak');
INSERT INTO CUSTOMER VALUES ('C004','Howard','6/24/1997','Johor');
INSERT INTO CUSTOMER VALUES ('C005','Torres','8/3/1983','Negeri Sembilan');
INSERT INTO CUSTOMER VALUES ('C006','Peterson','12/31/1990','Kedah');
INSERT INTO CUSTOMER VALUES ('C007','Gray','5/20/1999','');
INSERT INTO CUSTOMER VALUES ('C008','James','','');
INSERT INTO CUSTOMER VALUES ('C009','Watson','10/9/1993','Sabah');
INSERT INTO CUSTOMER VALUES ('C010','Brooks','9/17/1989','Terengganu');
INSERT INTO CUSTOMER VALUES ('C011','Kelly','8/23/1997','Perlis');
INSERT INTO CUSTOMER VALUES ('C012','Wendy','','');
INSERT INTO CUSTOMER VALUES ('C013','Perry','7/18/1993','Selangor');
INSERT INTO CUSTOMER VALUES ('C014','Alexander','2/13/1980','Kelantan');
INSERT INTO CUSTOMER VALUES ('C015','Gladys','','Sarawak');

When I run the program and I use the

select * from CUSTOMER

all the CUST_STATE from my table without any insertion value comes out a " - " instead of a default value 'NOT STATED', but when I run my program no errors were detected.

Answer 1

If you really want the column to default to a non-null value, even if the INSERT statement has NULL for it, you can use the DEFAULT ON NULL syntax, e.g.:

ALTER TABLE CUSTOMER MODIFY CUST_STATE DEFAULT ON NULL 'NOT STATED';

or, if you are creating the table from scratch:

CREATE TABLE CUSTOMER
(
CUST_ID     VARCHAR(10),
CUST_NAME   VARCHAR(20)   NOT NULL UNIQUE,
CUST_DOB    DATE          NULL,               
CUST_STATE  VARCHAR(20)   DEFAULT ON NULL 'NOT STATED',
PRIMARY KEY(CUST_ID)
);

Now, when you insert a row with NULL for that column:

INSERT INTO CUSTOMER VALUES ('C007','Gray','5/20/1999','');

The row will have 'NOT STATED' for CUST_STATE.

Note: this "NOT STATED" is known as a "magic value" and is generally considered bad practice. It would be better, if you want to show "NOT STATED" on the screen if no value was entered, to use a SQL expression such as NVL(CUST_STATE,'NOT STATED') at query time.