Ytukyg

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.

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.

This is what you are currently doing (C007 doesn't have the CUST_STATE):

SQL> insert into customer (cust_id, cust_name, cust_dob, cust_state)

  2    values ('C007','Gray','5/20/1999', '');



1 row created.



SQL> select * from customer where cust_id = 'C007';



CUST_ID    CUST_NAME            CUST_DOB   CUST_STATE

---------- -------------------- ---------- --------------------

C007       Gray                 05/20/1999



SQL>

As you can see, CUST_STATE is empty, while you expected 'NOT STATED'.

What's wrong with the INSERT? Almost nothing; it is expected behavior. Oracle will use column's default value only if you don't specify value to be inserted into that column. You, on the other hand, said that you want to put an empty string ('') into that column so default value was never used.

However, if you omit CUST_STATE column's value entirely, it'll work as you'd want it to:

SQL> delete from customer where cust_id = 'C007';



1 row deleted.



SQL> insert into customer (cust_id, cust_name, cust_dob)

  2    values ('C007','Gray','5/20/1999');



1 row created.



SQL> select * from customer where cust_id = 'C007';



CUST_ID    CUST_NAME            CUST_DOB   CUST_STATE

---------- -------------------- ---------- --------------------

C007       Gray                 05/20/1999 NOT STATED



SQL>

So, rewrite those INSERT INTO statements. Generally speaking, you should always name all columns you're using. True, it requires some more typing, but - doing so - you have control over the process.