Re: DEFAULT clause without NOT NULL?
On Sun, 29 Aug 2004 22:26:25 +0100, Tim Bunce [EMAIL PROTECTED] wrote: Do any databases support CREATE TABLE statement with fields having a DEFAULT clause without a NOT NULL? CREATE TABLE foo ( bar INTEGER, baz INTEGER DEFAULT 42 ) Yes. Informix does. and if so, under what circumstances is the default applied? When no value is supplied for the column in the insert statement. INSERT INTO foo(baz) VALUES(12); -- baz gets the default! I can imagine it meaning that INSERT INTO foo (bar, baz) VALUES (1, NULL) doesn't trigger the DEFAULT, but that Correct: a legitimate value - NULL - is supplied and stored. INSERT INTO foo (bar) VALUES (1) would. Correct. But I'm just guessing. I'm not aware of any that do that as I've never looked into it before. Tim [trying to write a book, it seems] Good luck with the writing... -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org I don't suffer from insanity - I enjoy every minute of it.
RE: DEFAULT clause without NOT NULL?
Do any databases support CREATE TABLE statement with fields having a DEFAULT clause without a NOT NULL? Oracle and MySQL do, at least. [EMAIL PROTECTED] src]$ sqlplus test/test SQL*Plus: Release 9.2.0.5.0 - Production on Mon Aug 30 00:13:29 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning option JServer Release 9.2.0.5.0 - Production SQL CREATE TABLE foo ( 2 bar INTEGER, 3 baz INTEGER DEFAULT 42 4 ) 5 / Table created. SQL desc foo; Name Null?Type - BARNUMBER(38) BAZNUMBER(38) SQL INSERT INTO foo (bar, baz) VALUES (1, NULL); 1 row created. SQL INSERT INTO foo (bar) VALUES (1); 1 row created. SQL commit; Commit complete. SQL select * from foo; BARBAZ -- -- 1 1 42 [EMAIL PROTECTED]:~$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.0.18-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql connect test; Connection id:7 Current database: test mysql CREATE TABLE foo ( - bar INTEGER, - baz INTEGER DEFAULT 42 - ); Query OK, 0 rows affected (0.03 sec) mysql INSERT INTO foo (bar, baz) VALUES (1, NULL); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO foo (bar) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql select * from foo; +--+--+ | bar | baz | +--+--+ |1 | NULL | |1 | 42 | +--+--+ 2 rows in set (0.00 sec) -- Andy Hassall [EMAIL PROTECTED] / Space: disk usage analysis tool http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space
Re: DEFAULT clause without NOT NULL?
On Sun, 29 Aug 2004 20:50:05 -0700, Darren Duncan [EMAIL PROTECTED] wrote: At 8:09 PM -0700 8/29/04, Jonathan Leffler wrote: Darren Duncan wrote: At 6:53 PM -0400 8/29/04, Paul G. Weiss wrote: MySQL works just as you describe. -P Even so, it is on the MySQL to-do list that such non-standard behaviour is going to be changed. Hopefully sooner rather than later. -- Darren Why break a perfectly sensible system? I'll quote some sections from the MySQL TODO, to illustrate what I'm saying. Pay particular attention to: * Don't add automatic DEFAULT values to columns. Produce an error for any INSERT statement that is missing a value for a column that has no DEFAULT . My read of this statement does not rule out the current behavior of allowing default values for nullable columns. Instead it would prevent: create table foo (a varchar null, bar integer not null) from silently becoming create table foo (a varchar null, bar integer not null default 0) as it does now, and so insert into foo (a) values (x) would be considered an error, rather than an insert of (x,0) as it is now.
