SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default value in DECIMAL field

    I see that the default value in a field of type DECIMAL is '0.00'. I would like to have an empty field as the default value, how can I do this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    specify NULL instead of NOT NULL, with no DEFAULT
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried doing that, but it nothing changes, not even if I erase the fields manually. Everytime the default value goes back to zero.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    then you're doing something else wrong

    please do a SHOW CREATE TABLE for your table, and show us how you insert a row

    then compare to this --
    Code:
    CREATE TABLE foobar
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , foo DECIMAL(7,2) NOT NULL DEFAULT 0.00
    , bar DECIMAL(7,2) NULL 
    );
    
    INSERT INTO foobar (foo) VALUES ( 9.37 );
    
    INSERT INTO foobar (bar) VALUES ( 9.37 );
    
    SELECT * FROM foobar;
    any guess as to what this code produces?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the output of SHOW CREATE TABLE (just the part of interest):

    Code:
    CREATE TABLE `appuntamenti` (
      `nome` varchar(255) NOT NULL,
      `data` date NOT NULL,
      `affarilordo` decimal(10,2) NOT NULL,
      `imponibile` decimal(10,2) default NULL,
      `note` text NOT NULL,
      PRIMARY KEY  (`nome`,`data`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    About your code, I'm probably wrong, but it should insert the value 9.37 in both the foo and the bar fields. Right? No XD

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    everything here works exactly as expected --
    Code:
    INSERT INTO appuntamenti VALUES
     ( 'nome1' , '2009-09-09' , 9.37 , NULL , 'whoa' )
    ,( 'nome2' , CURRENT_DATE , 0.00 , 9.37 , 'whoa' )
    ;
    INSERT INTO appuntamenti 
     ( nome , data , affarilordo , note )
    VALUES
     ( 'nome3' , '2009-09-09' , 9.37 , 'whoa' )
    ;
    INSERT INTO appuntamenti 
     ( nome , data , imponibile , note )
    VALUES
     ( 'nome4' , '2009-09-09' , 9.37 , 'whoa' )
    ;
    SELECT * FROM appuntamenti
    ;
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uhm, yes, you are right. It's probably some Javascript code that makes me see it as 0 instead than as blank...

    Thank you


Bookmarks

Posting Permissions

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