Thread: primary key question
create table aaa(field1 .... not null,field2 ....,....,primary key (field1, field2) ); Based on the above table definition, field2 allows null values. But after the table created based on the above script, field2 becomes not null. The only conclusion I come up with is setting the field as part of the primary key, PostgreSQL automatically sets the field to not null. Or is it something else? Is this a feature or bug?
Carolyn Lu Wong wrote: > > create table aaa( > field1 .... not null, > field2 ...., > ...., > primary key (field1, field2) > ); > > Based on the above table definition, field2 allows null values. But > after the table created based on the above script, field2 becomes not > null. The only conclusion I come up with is setting the field as part of > the primary key, PostgreSQL automatically sets the field to not null. Or > is it something else? > > Is this a feature or bug? This is in sync with the SQL-92 spec as the following explains: Quote from Tom Lane on pgsql-general yesterday: > Two nulls are never considered equal, therefore the unique constraint > does not trigger. > > This is correct behavior according to SQL92 4.10.2: > > A unique constraint is satisfied if and only if no two rows in > a table have the same non-null values in the unique columns. In > ^^^^^^^^ > addition, if the unique constraint was defined with PRIMARY KEY, > then it requires that none of the values in the specified column or > columns be the null value. > > (The second sentence just says that PRIMARY KEY implies NOT NULL as well > as UNIQUE.) > > Another way to look at it is that the comparison to see whether the two > NULLs are equal would yield NULL, and a NULL result for a constraint > condition is not considered to violate the constraint. > > Another way to look at it is that NULL means "I don't know what the > value is", so if you don't know what the values in two rows really are, > you don't know whether they're equal either. I suppose you could make > a case for either accepting or rejecting the UNIQUE constraint in that > situation --- but SQL92 chose the "accept" decision, and I think that > for the majority of practical applications they made the right choice. > > If you don't like that behavior, possibly your column should be defined > as NOT NULL. > Regards Wim
>From: Carolyn Lu Wong <carolyn@kss.net.au> >To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org> >Subject: [SQL] primary key question >Date: Thu, 20 Jul 2000 17:26:30 +1000 > >create table aaa( > field1 .... not null, > field2 ...., > ...., > primary key (field1, field2) >); > >Based on the above table definition, field2 allows null values. But >after the table created based on the above script, field2 becomes not >null. The only conclusion I come up with is setting the field as part of >the primary key, PostgreSQL automatically sets the field to not null. Or >is it something else? > >Is this a feature or bug? Sure it is a feature , since the first integrity rule force the primary key(s) to be not null automatically whether entirely or partially. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 El 20-Jul-2000 Carolyn Lu Wong escribio: > create table aaa( > field1 .... not null, > field2 ...., > ...., > primary key (field1, field2) > ); > > Based on the above table definition, field2 allows null values. But > after the table created based on the above script, field2 becomes not > null. The only conclusion I come up with is setting the field as part of > the primary key, PostgreSQL automatically sets the field to not null. Or > is it something else? > > Is this a feature or bug? It's a SQL feature, because primary key must no contain null values. Pablo Niklas pniklas@bigfoot.com -----BEGIN PGP SIGNATURE----- Version: PGPfreeware 5.0i for non-commercial use Charset: noconv iQA/AwUBOXg9Mozs62hO+t8PEQIsGgCdFO5HlMTy087WsBFc3tPSGFBKGOUAni6C 2NnOSwcsnlNFrkNsPoV1N3u7 =Ex7i -----END PGP SIGNATURE-----