This is one strange, unwanted behavior I encountered in Postgres: When I create a Postgres table with composite primary keys, it enforces NOT NULL constraint on each column of the composite combination.
For example,
CREATE TABLE distributors (m_id integer, x_id integer, PRIMARY KEY(m_id, x_id));
enforces NOT NULL
constraint on columns m_id
and x_id
, which I don't want! MySQL doesn't do this. I think Oracle doesn't do it as well.
I understand that PRIMARY KEY
enforces UNIQUE
and NOT NULL
automatically but that makes sense for single-column primary key. In a multi-column primary key table, the uniqueness is determined by the combination.
Is there any simple way of avoiding this behavior of Postgres?
If I execute this:
CREATE TABLE distributors (m_id integer, x_id integer);
I do not get any NOT NULL
constraints of course.
Hi, In composite primary key columns you cannot pass null values. Each column defined as a primary key would be validated so that null values are not passed on to them.
Primary key must not include nullable columns. auto_increment is not a check constraint, (it is rather a default constraint) , so you cannot remove not null from definition of the column that is part of primary key regardless of presence of auto_increment .
Yes. It is. Just put a constraint on the table. CREATE TEMP TABLE foo ( userid serial PRIMARY KEY, ipv4 inet CHECK (family(ipv4) = 4), ipv6 inet CHECK (family(ipv6) = 6), CHECK (ipv4 IS NOT NULL OR ipv6 IS NOT NULL) );
If you need to allow NULL values, use a UNIQUE
constraint instead of a PRIMARY KEY
(and add a surrogate PK column, I suggest a serial
). This allows columns to be NULL:
CREATE TABLE distributor ( distributor_id serial PRIMARY KEY , m_id integer , x_id integer , UNIQUE(m_id, x_id) );
Note, however (per documentation):
For the purpose of a unique constraint, null values are not considered equal.
In your case, you could enter something like (1, NULL)
for (m_id, x_id)
any number of times without violating the constraint. Postgres never considers two NULL values equal - as per definition in the SQL standard.
If you need to treat NULL
values as equal to disallow such "duplicates", I see two options:
In addition to the UNIQUE
constraint above:
CREATE UNIQUE INDEX dist_m_uni_idx ON distributor (m_id) WHERE x_id IS NULL; CREATE UNIQUE INDEX dist_x_uni_idx ON distributor (x_id) WHERE m_id IS NULL;
But this gets out of hands quickly with more than two columns that can be NULL. See:
UNIQUE
index on expressionsInstead of the UNIQUE constraint. We need a free default value that is never present in involved columns, like -1
. Add CHECK
constraints to disallow it:
CREATE TABLE distributor ( distributor serial PRIMARY KEY , m_id integer , x_id integer , CHECK (m_id <> -1) , CHECK (x_id <> -1) );
CREATE UNIQUE INDEX distributor_uni_idx ON distributor (COALESCE(m_id, -1) , COALESCE(x_id, -1))
How certain RDBMS handle things isn't always a useful indicator for proper behavior. The Postgres manual hints at this:
That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.
Bold emphasis mine.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With