Thread: Is this logical?
Hi, Shouldn't this create statement trigger an error? create table bar (col1 int not null default null); Shouldn't I be forbidden to insert null values into a non null column? Regards, Behi -- "We can only see a short distance ahead, but we can see plenty there that needs to be done." - Alan Turing "Science is a differential equation. Religion is a boundary condition" - Alan Turing Behrang Saeedzadeh http://www.jroller.com/page/behrangsa http://my.opera.com/behrangsa
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Behrang Saeedzadeh wrote: > Hi, > > Shouldn't this create statement trigger an error? > > create table bar (col1 int not null default null); > > Shouldn't I be forbidden to insert null values into a non null column? What happens when you actually try to insert a record? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFAzbfS9HxQb37XmcRAq6zAJ0SeZKRrHScWhVIwf3F7gZXTdbUQwCgyo7n TZd05gua4cmoz4KMe01M5HQ= =Zf4H -----END PGP SIGNATURE-----
On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: > Shouldn't this create statement trigger an error? > > create table bar (col1 int not null default null); > > Shouldn't I be forbidden to insert null values into a non null column? I think it should forbid it when the default actually comes into play like on insert or update, but not at create table time as there are no rows for which the constraint fails. For example after that: sszabo=# insert into bar default values; ERROR: null value in column "col1" violates not-null constraint
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote:
>> Shouldn't this create statement trigger an error?
>> create table bar (col1 int not null default null);
> I think it should forbid it when the default actually comes into play like
> on insert or update, but not at create table time as there are no rows for
> which the constraint fails.
The SQL spec defines the not-null clause as a check constraint:
a) If a <column constraint definition> is specified that
contains the <column constraint> NOT NULL, then it is
equivalent to the following <table constraint definition>:
CND CHECK ( C IS NOT NULL ) CA
It doesn't say anything about a nonnull default being required. In fact,
since what you wrote is exactly equivalent to
create table bar (col1 int not null);
(that is, "default null" is the default), you would have to say that NOT
NULL requires a non-null DEFAULT to be specified, and that is certainly
not in the spec; indeed NOT NULL with no default is a customary usage to
be sure that people specify a required column during INSERT.
regards, tom lane
On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: > But "not null" is in contradiction with "default null" so the create > statement should not proceed successfuly IMHO. The fact that the default value isn't going to pass a constraint means that it's invalid to not provide a value or use default, yes, but I don't agree that it's necessarily a real contradiction in the definition, especially given that AFAIK there's no way to opt out of having a default (apart from a construct like the one given).
Stephan, But "not null" is in contradiction with "default null" so the create statement should not proceed successfuly IMHO. Regards, Behi On 9/10/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: > > > Shouldn't this create statement trigger an error? > > > > create table bar (col1 int not null default null); > > > > Shouldn't I be forbidden to insert null values into a non null column? > > I think it should forbid it when the default actually comes into play like > on insert or update, but not at create table time as there are no rows for > which the constraint fails. > > For example after that: > sszabo=# insert into bar default values; > ERROR: null value in column "col1" violates not-null constraint > -- "We can only see a short distance ahead, but we can see plenty there that needs to be done." - Alan Turing "Science is a differential equation. Religion is a boundary condition" - Alan Turing Behrang Saeedzadeh http://www.jroller.com/page/behrangsa http://my.opera.com/behrangsa
Hi, Actually I read in a blog that this works in PostgreSQL (the author expected this) and not in MySQL. But to me, the way MySQL is handling this looks more reasonable. You may want to read the blog here: http://www.redhillconsulting.com.au/blogs/simon/archives/000347.html Regards, Behi On 9/10/06, Ron Johnson <ron.l.johnson@cox.net> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Behrang Saeedzadeh wrote: > > Hi, > > > > Shouldn't this create statement trigger an error? > > > > create table bar (col1 int not null default null); > > > > Shouldn't I be forbidden to insert null values into a non null column? > > What happens when you actually try to insert a record? > > - -- > Ron Johnson, Jr. > Jefferson LA USA > > Is "common sense" really valid? > For example, it is "common sense" to white-power racists that > whites are superior to blacks, and that those with brown skins > are mud people. > However, that "common sense" is obviously wrong. > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.5 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFFAzbfS9HxQb37XmcRAq6zAJ0SeZKRrHScWhVIwf3F7gZXTdbUQwCgyo7n > TZd05gua4cmoz4KMe01M5HQ= > =Zf4H > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- "We can only see a short distance ahead, but we can see plenty there that needs to be done." - Alan Turing "Science is a differential equation. Religion is a boundary condition" - Alan Turing Behrang Saeedzadeh http://www.jroller.com/page/behrangsa http://my.opera.com/behrangsa
"Behrang Saeedzadeh" <behrangsa@gmail.com> writes:
> Actually I read in a blog that this works in PostgreSQL (the author
> expected this) and not in MySQL. But to me, the way MySQL is handling
> this looks more reasonable.
MySQL's way of doing things is seldom reasonable. In the example
given, they are clearly inventing a default value out of thin air.
There is *no* support for that in the SQL spec.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Behrang Saeedzadeh wrote: > Stephan, > > But "not null" is in contradiction with "default null" so the create > statement should not proceed successfuly IMHO. I see your point, but those are run-time (i.e. DML) operations, not DDL statements. Added complexity would have to be added to the parser, and added complexity makes for more bugs. > Regards, > Behi > > On 9/10/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: >> On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: >> >> > Shouldn't this create statement trigger an error? >> > >> > create table bar (col1 int not null default null); >> > >> > Shouldn't I be forbidden to insert null values into a non null column? >> >> I think it should forbid it when the default actually comes into play >> like >> on insert or update, but not at create table time as there are no rows >> for >> which the constraint fails. >> >> For example after that: >> sszabo=# insert into bar default values; >> ERROR: null value in column "col1" violates not-null constraint - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFA2yzS9HxQb37XmcRAp77AJ4jhCy2kWu89QbG+D/O+3DVRO9q7gCaApXU HE4hItgv6f0LH2OQEyIVV/A= =9IQY -----END PGP SIGNATURE-----
If there is no default statement, then the default (by default) is null. What you are suggesting is that every field that is not null should require a default value. Behrang Saeedzadeh wrote: > Stephan, > > But "not null" is in contradiction with "default null" so the create > statement should not proceed successfuly IMHO. > > Regards, > Behi > > On 9/10/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: >> On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: >> >> > Shouldn't this create statement trigger an error? >> > >> > create table bar (col1 int not null default null); >> > >> > Shouldn't I be forbidden to insert null values into a non null column? >> >> I think it should forbid it when the default actually comes into play >> like >> on insert or update, but not at create table time as there are no rows >> for >> which the constraint fails. >> >> For example after that: >> sszabo=# insert into bar default values; >> ERROR: null value in column "col1" violates not-null constraint >> > >
At 01:12 AM 9/10/2006 +0330, Behrang Saeedzadeh wrote: >Hi, > >Shouldn't this create statement trigger an error? > > create table bar (col1 int not null default null); No. >Shouldn't I be forbidden to insert null values into a non null column? Yes. Use "not null default null" when you want to force apps/people etc to explicitly specify values when inserting. Link.