Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Thu, 31 Aug 2006 13:48:26 +0200
From: "Bernt M. Johnsen" <Bernt.Johnsen@Sun.COM>
Subject: Re: SQL Parser failing on NULL column contraint
In-reply-to: <44F6B856.9070503@xtra.co.nz>
Sender: Bernt.Johnsen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Reply-to: Bernt.Johnsen@Sun.COM
Message-id: <44F6CC8A.50706@Sun.COM>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: multipart/signed;
 boundary=------------enig4C8DEF0E7EE572B2C836F1C9;
 protocol="application/pgp-signature"; micalg=pgp-sha1
References: <44F3087F.1020006@intland.com> <44F34597.4090902@gmail.com>
 <ee564160608291908sc0ba1abi20b5614d9cf503fa@mail.gmail.com>
 <44F587AA.8060007@xtra.co.nz> <44F5AE95.2010907@amberpoint.com>
 <44F67DEB.2040103@xtra.co.nz> <44F6B856.9070503@xtra.co.nz>
User-Agent: Thunderbird 1.5.0.4 (X11/20060516)


--------------enig4C8DEF0E7EE572B2C836F1C9
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Duncan Groenewald wrote:
> Point 5 is the reason,  one of the databases I use does not handle no
> definition and requires a NULL or  NOT NULL definition.  But its been a=

> long time since I checked this so the newer version may be more
> compliant.  I'll check before I waste any more time on this.
>
> Interestingly derby is the first database I have encountered(I think)
> that throws an error on a NULL definition.

I did a brief survey on the net, and found that MySQL, Sybase,
PostgreSQL, HSQLDB, H2, Oracle and SQLServer allows the user to
specify the non-standard "constraint" NULL. PostgreSQL has the most
sensible note in the docs:

"This clause is only available for compatibility with non-standard SQL
databases. Its use is discouraged in new applications."

DB2 and Derby does not allow NULL.

And Sybase defaults to NOT NULL (in default configuration and as you
point out in another mail) which is definitely against the SQL
standard, and that means that portable CREATE TABLE statements will
not work on Sybase (even if they do syntactically).

And, you can't write SQL which is compatible between Sybase (in
default configuration) and Derby (or DB2 for that matter).

--=20
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway


--------------enig4C8DEF0E7EE572B2C836F1C9
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.4 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE9syOlFBD9TXBAPARAjqXAKDEw2ezDKiWutAOMgdsNQ9H35p9lgCgyKng
6f2t80LwBtWD+biL6YFIl7s=
=ui2L
-----END PGP SIGNATURE-----

--------------enig4C8DEF0E7EE572B2C836F1C9--