NOT NULL and Default
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: NOT NULL and Default

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    Whats the use of having default and not null constraint on the same column, wouldn't default make that column NOT NULL..
    CREATE TABLE AttribBill (
    AtBill_ID number PRIMARY KEY ,
    AtBill_Type number NOT NULL,
    AtBill_Date date NULL ,
    AtBill_Invoiced number Default 20 Not Null);
    AtBill_Invoiced number Default 20 Not Null... here this column has both... also

    When do you do default and NULL on the same column ??

    SAy
    AtBill_Invoiced number Default 20 Null

    Isn't this technically wrong ?

    Thanks
    Sonali
    Sonali

  2. #2
    Join Date
    Apr 2001
    Posts
    118
    I wouldn't consider it wrong. Even though the column may default to a known value, it may be perfectly reasonable for that column to be updated later to an unknown value (NULL).

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Null will take the precedence anyways, so it's not technically wrong just logically contradictory.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    Are you saying that default works only during insert and not during update ?

    Thanks
    Sonali
    Sonali

  5. #5
    Join Date
    Apr 2001
    Posts
    118
    I guess you could state it that way. Default values only are used for INSERT statements where the DML does not explicitly set the value for the column. If you don't specify the column in an UPDATE statement, then its value will not get changed at all. Any default value for the column is irrelevant in that case.

    Code:
    me@myDB>create table table1( a varchar2(5) not null, 
                                 b number default 20 null );
    
    Table created.
    
    me@myDB>select * from table1;
    
    no rows selected
    
    me@myDB>-- Do not specify a value for column b.
    me@myDB>-- The default value for the column will be used.
    me@myDB>insert into table1(a) values('x');
    
    1 row created.
    
    me@myDB>select * from table1;
    
    A              B
    ----- ----------
    x             20
    
    me@myDB>-- Set column b to NULL.
    me@myDB>update table1 set b = NULL;
    
    1 row updated.
    
    me@myDB>select * from table1;
    
    A              B
    ----- ----------
    x
    
    me@myDB>-- Update table.  
    me@myDB>-- Do not specify a value for column b.
    me@myDB>-- Default value is irrelevant.
    me@myDB>update table1 set a='y';
    
    1 row updated.
    
    me@myDB>select * from table1;
    
    A              B
    ----- ----------
    y
    HTH,

    Heath

  6. #6
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    From Sonali's mail
    "When do you do default and NULL on the same column ?? "

    I implement security like the following:

    Assume that there is a table with 15 columns.
    There are different types of users and according to their privileges the users will see different no. of columns.

    So, I have created 3 views and created synonym's on these views through which the user's will insert the records.
    1st view: 5 columns(one column has a default value and not null constraint for which this user does not
    have an access)
    2nd view: 10 columns
    3rd view: 13 columns

    In this case what I do is, give the default value as NA, so that this value will be updated by 2nd views owner or third view's owner.

    Hope I have not confused you.

    Vijay.
    Say No To Plastics

  7. #7
    Join Date
    Dec 2000
    Posts
    126
    SQL> create table table1( a varchar2(5) not null,
    2 b number default 20 null ,
    3 c varchar2(5) default 'x' not null
    4 )
    5 ;

    Table created.

    SQL> insert into table1(a)
    2 values('a')
    3 ;

    1 row created.


    SQL> select *
    2 from table1
    3 ;

    A B C
    ----- ---------- -----
    a 20 x



    *** try to update column c with NULL value ***

    SQL> update table1
    2 set c=''
    3 ;
    update table1
    *
    ERROR at line 1:
    ORA-01407: cannot update ("DWDBA"."TABLE1"."C") to NULL


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width