Home » SQL & PL/SQL » SQL & PL/SQL » DEFAULT value (Oracle 9.2.0.3)
DEFAULT value [message #378496] Tue, 30 December 2008 14:37 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
SQL> create table mytable (
2 last_name varchar2(30) not null,
3 first_name varchar2(30) not null,
4 rating smallint default 1);



When we specify default NOT NULL contraint should be added.

Want to know if I am wrong here...

[Updated on: Tue, 30 December 2008 14:40]

Report message to a moderator

Re: DEFAULT value [message #378498 is a reply to message #378496] Tue, 30 December 2008 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are wrong.
What did your example give you?

Regards
Michel
Re: DEFAULT value [message #378499 is a reply to message #378498] Tue, 30 December 2008 15:04 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
What I understood is if your business rule states that for a column you must always know a value, then sure: use NOT NULL. It's what it's there for. But it is frequently the case that things will be unknown at the time of data entry, and if so, a not null constraint is entirely inappropriate, and the explicit assignment of NULL to a value is better practice than just remaining silent on the matter.

Regards,
Oli
Re: DEFAULT value [message #378500 is a reply to message #378496] Tue, 30 December 2008 15:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Olivia wrote on Tue, 30 December 2008 21:37
When we specify default NOT NULL contraint should be added.

Want to know if I am wrong here...

So specify NOT NULL constraint explicitly. I do not know, what you expect, but DEFAULT value and NOT NULL constraint are two different things.
SQL> insert into mytable (last_name, first_name)
  2  values ('default', 'default value');

1 row created.

SQL> insert into mytable (last_name, first_name, rating)
  2  values ('specified', 'non-default null', null);

1 row created.

SQL> select * from mytable;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
default                        default value                           1
specified                      non-default null

2 rows selected.

Re: DEFAULT value [message #378505 is a reply to message #378500] Tue, 30 December 2008 15:15 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
hr@ORAFAQ.US.ORACLE.COM> create table mytable (
  2   last_name varchar2(30) not null,
  3   first_name varchar2(30) not null,
  4   rating smallint default 1);

Table created.

Elapsed: 00:00:00.06
hr@ORAFAQ.US.ORACLE.COM> insert into mytable values ('Stefens', 'John', null);

1 row created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> select * from mytable;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
Stefens                        John

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> insert into mytable values ('Stefens', 'John');
insert into mytable values ('Stefens', 'John')
            *
ERROR at line 1:
ORA-00947: not enough values


Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> insert into mytable (last_name, first_name) values ('Stefens', 'John');

1 row created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> select * from mytable;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
Stefens                        John
Stefens                        John                                    1

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> insert into mytable (last_name, first_name, rating) values ('Stefens', 'Joh
n', null);

1 row created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> select * from mytable;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
Stefens                        John
Stefens                        John                                    1
Stefens                        John

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> 

i.e, If we pass null then rating is null
if we pass nothing then defaulyt is 1


=>  NULL does n't imply nothing!!


Re: DEFAULT value [message #378506 is a reply to message #378496] Tue, 30 December 2008 15:19 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Olivia wrote on Tue, 30 December 2008 15:37

Want to know if I am wrong here...


Just because you have a default doesn't mean you cannot have a NULL.

FOO SCOTT>create table mytable (
  2  last_name varchar2(30) not null,
  3  first_name varchar2(30) not null,
  4  rating smallint default 1);

Table created.

FOO SCOTT>insert into mytable (last_name,first_name,rating) values ('A','B',null);

1 row created.
FOO SCOTT>select * from mytable;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
A                              B


Re: DEFAULT value [message #378511 is a reply to message #378506] Tue, 30 December 2008 15:26 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Agree...
But a bit confused here.
Two cases:
1. Nothing has been passed for rating
2. Null passed for rating

Whats the difference between this two term nothing and null then?


Regards
Oli

[Updated on: Tue, 30 December 2008 15:29]

Report message to a moderator

Re: DEFAULT value [message #378514 is a reply to message #378511] Tue, 30 December 2008 15:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULL is not nothing, NULL is unknown/undefined.

Regards
Michel
Re: DEFAULT value [message #378774 is a reply to message #378514] Thu, 01 January 2009 12:30 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am bit confused here...

If null means 'don't know' then value should have replaced by default value!!!

hr@ORAFAQ.US.ORACLE.COM> create table mytable010109 (
  2    last_name varchar2(30) not null,
  3      first_name varchar2(30) not null,
  4      rating number );

Table created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> 
hr@ORAFAQ.US.ORACLE.COM> ----I dont know the rating
hr@ORAFAQ.US.ORACLE.COM> insert into mytable010109 (last_name,first_name,rating) values ('A','B',nul
l);

1 row created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> --- I may  know the value (but dont want to pass) or may not know the value
 to pass (?)
hr@ORAFAQ.US.ORACLE.COM> insert into mytable010109 (last_name,first_name) values ('A','B');

1 row created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> 
hr@ORAFAQ.US.ORACLE.COM> select * from mytable010109;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
A                              B
A                              B


---Now, in 2nd case where I have used DEFAULT for the column rating..

hr@ORAFAQ.US.ORACLE.COM> create table mytable010109_1 (
  2    last_name varchar2(30) not null,
  3      first_name varchar2(30) not null,
  4      rating number DEFAULT 3);

Table created.

Elapsed: 00:00:00.00

hr@ORAFAQ.US.ORACLE.COM> insert into mytable010109_1 (last_name,first_name,rating) values ('A','B',n
ull);

1 row created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> insert into mytable010109_1 (last_name,first_name) values ('A','B');

1 row created.


--Here's the confusion:
--DEFAULT is something that If I dont know the value or miss the value then
it should insert 3 by default.

http://www.orafaq.com/forum/mv/msg/138417/378725/125380
Quote:


Null means 'dont know'...



If I dont know the value to insert i.e,
 
 --insert into mytable010109_1 (last_name,first_name,rating) values ('A','B'
,null);

then 3 should be inserted by default!!!

hr@ORAFAQ.US.ORACLE.COM> select * from mytable010109_1;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
A                              B
A                              B                                       3

Elapsed: 00:00:00.00

[Updated on: Thu, 01 January 2009 12:30]

Report message to a moderator

Re: DEFAULT value [message #378776 is a reply to message #378774] Thu, 01 January 2009 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From the other topic:
Michel Cadot wrote on Thu, 01 January 2009 19:31
DEFAULT means when you don't give a value.
Giving NULL is NOT not giving value, it is saying "I don't know" or "the value is not defined or meaningless for this row".

Regards
Michel

Re: DEFAULT value [message #378777 is a reply to message #378776] Thu, 01 January 2009 12:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

hr@ORAFAQ.US.ORACLE.COM> ----case1: I dont know the rating
hr@ORAFAQ.US.ORACLE.COM> insert into mytable010109 (last_name,first_name,rating) values ('A','B',nul
l);

1 row created.

Elapsed: 00:00:00.00
hr@ORAFAQ.US.ORACLE.COM> --- case2: I may  know the value (but dont want to pass) or may not know the value
 to pass (?)
hr@ORAFAQ.US.ORACLE.COM> insert into mytable010109 (last_name,first_name) values ('A','B');

1 row created.

hr@ORAFAQ.US.ORACLE.COM> select * from mytable010109 where rating IS NULL;

LAST_NAME                      FIRST_NAME                         RATING
------------------------------ ------------------------------ ----------
A                              B
A                              B


Here two rows...i,e, for both cases NULL there ..when we did pass 'nothing' then also null is there!!
Based on this,(I am confused here)
So my only concern is when null is passed default value should have entered..

[Updated on: Thu, 01 January 2009 12:53]

Report message to a moderator

Re: DEFAULT value [message #378781 is a reply to message #378777] Thu, 01 January 2009 14:19 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you have no default value, so it is undefined, that is NULL.

Regards
Michel
Previous Topic: how null values are stored
Next Topic: Tables with Composite Indexes
Goto Forum:
  


Current Time: Mon Dec 05 20:58:17 CST 2016

Total time taken to generate the page: 0.09984 seconds