Is this logical?

-----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

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

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

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 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

"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:

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.