Received: from uni-box.nice.be (IDENT:0@nice.be [195.144.66.92]) by hub.org (8.10.1/8.10.1) with ESMTP id e6K7a0h94719 for ; Thu, 20 Jul 2000 03:36:01 -0400 (EDT) Received: from nice.be ; Thu, 20 Jul 2000 09:35:36 +0200 Message-ID: <3976AE1B.C8BD2CEE@nice.be> Date: Thu, 20 Jul 2000 09:45:31 +0200 From: Wim Ceulemans X-Mailer: Mozilla 4.72 [en] (X11; I; Linux 2.2.13 i686) X-Accept-Language: en MIME-Version: 1.0 To: Carolyn Lu Wong CC: "pgsql-sql@postgresql.org" Subject: Re: primary key question References: <3976A9A6.55E6BE2E@kss.net.au> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200007/190 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