Recipe4.3.Overriding a Default Value with NULL


Recipe 4.3. Overriding a Default Value with NULL

Problem

You are inserting into a column having a default value, and you wish to override that default value by setting the column to NULL. Consider the following table:

 create table D (id integer default 0, foo VARCHAR(10)) 

You wish to insert a row with a NULL value for ID.

Solution

You can explicitly specify NULL in your values list:

 insert into d (id, foo) values (null, 'Brighten') 

Discussion

Not everyone realizes that you can explicitly specify NULL in the values list of an INSERT statement. Typically, when you do not wish to specify a value for a column, you leave that column out of your column and values lists:

 insert into d (foo) values ('Brighten') 

Here, no value for ID is specified. Many would expect the column to taken on the null value, but, alas, a default value was specified at table creation time, so the result of the preceding INSERT is that ID takes on the value 0 (the default). By specifying NULL as the value for a column, you can set the column to NULL despite any default value.




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net