Hi! On Tue, Dec 16, 2008 at 8:46 PM, Roy Lyseng <[email protected]> wrote: >> Agreed. If you specify NOT NULL without a default, then you are >> explicitly asking to only accept valid data. Munging it with fake "non >> data" like an empty string or a zero is something different when you have a >> default available to you. On the flip side, if you specify a default, then >> accepting NULL is fine; as it will of course use the default instead. >> >> However, even if the above may seem to be (hopefully) logical behavior, we >> don't want to make anyone's life harder. So having a default DEFAULT for a >> given data type may be reasonable if not defined on creation. With the >> ability to remove the default later if you explicitly want NULL to fail >> (which would likely be the minority case). Or use some idiom like >> specifying a self conflicting "NOT NULL DEFAULT NULL" to make a NULL fail on >> a NOT NULL field. That may be a bit too arcane though. >> > Actually, a DEFAULT value specified will still not overrule the attempt to > insert a NULL value into a non-nullable column. > > It is only when you omit the column name in the insert list or explicitly > specify the string DEFAULT that the default value will be taken.
Yes - I completely agree with Roy. If I read it correctly, the whole INSERT semantics are roughly as follows: 1) For each row that is to be inserted, a new 'candidate' row is to be created, initially with the default value for each column. Columns for which there is no direct or indirect user-specified default value will get NULL as the default value. 2) Columns having generated values are filled in with the generated value (which is generated at this moment), columns for which a value is specified, receive the specified value. 3) then BEFORE triggers are executed (and these may change the data) 4) row initiating the 'candidate' row is marked as processed. 5) at some stage, if all went well for all rows, 'candidate' rows are added to the table. > > Example: > CREATE TABLE T(A INT, B INT NOT NULL DEFAULT 13, C INT NOT NULL DEFAULT 14); > > INSERT INTO T(A, B) VALUES(NULL, DEFAULT); > > Thanks, > Roy > > _______________________________________________ > Mailing list: https://launchpad.net/~drizzle-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~drizzle-discuss > More help : https://help.launchpad.net/ListHelp > -- Roland Bouman http://rpbouman.blogspot.com/ _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
