How to set a default value when inserting null
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.
sql database oracle
add a comment |
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.
sql database oracle
add a comment |
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.
sql database oracle
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.
sql database oracle
sql database oracle
edited Nov 27 '18 at 2:33
Jeffrey Kemp
48.2k1189134
48.2k1189134
asked Nov 26 '18 at 17:17
computer NOOBscomputer NOOBs
133
133
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
add a comment |
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.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53486062%2fhow-to-set-a-default-value-when-inserting-null%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
add a comment |
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.
add a comment |
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.
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.
answered Nov 27 '18 at 2:32
Jeffrey KempJeffrey Kemp
48.2k1189134
48.2k1189134
add a comment |
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Nov 26 '18 at 18:04
LittlefootLittlefoot
22.8k71533
22.8k71533
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53486062%2fhow-to-set-a-default-value-when-inserting-null%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown