PostgreSQL Discussion - PRIMARY KEY on a *group* of columns imply that each column is NOT NULL?

Author

PRIMARY KEY on a *group* of columns imply that each column is NOT NULL?

Stephane Bortzmeyer

2005-04-26, 8:23 pm

If I define a primary key:

name TEXT NOT NULL,
address INET,
PRIMARY KEY(name, address)

the definition (seen by \d) becomes:

name | text | not null
address | inet | not null

"address" is now not null, which I do not want. It seems unnecessary:
I just want the tuple (name, address) to be unique, which seems
possible even if some 'address' values are NULL.

It does not appear to be documented in
http://www.postgresql.org/docs/7.4/...s.html#AEN1975.
Is there a workaround?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Guy Rouillier

2005-04-26, 8:23 pm

Stephane Bortzmeyer wrote:
> If I define a primary key:
>
> name TEXT NOT NULL,
> address INET,
> PRIMARY KEY(name, address)
>
> the definition (seen by \d) becomes:
>
> name | text | not null
> address | inet | not null
>
> "address" is now not null, which I do not want. It seems unnecessary:
> I just want the tuple (name, address) to be unique, which seems
> possible even if some 'address' values are NULL.
>
> It does not appear to be documented in
>
http://www.postgresql.org/docs/7.4/...aints.html#AEN1
975.
> Is there a workaround?

Per the SQL Commands Reference, under CREATE TABLE:

"The primary key constraint specifies that a column or columns of a
table may contain only unique (non-duplicate), nonnull values.
Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL"

Primary key columns cannot contain null values.

--
Guy Rouillier

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Sponsored Links