Thread: BUG #13665: Foreign Key constraint doesn't work

The following bug has been logged on the website:

Bug reference:      13665
Logged by:          Irina
Email address:      irina.guberman@gmail.com
PostgreSQL version: 9.4.4
Operating system:   Mac
Description:

I can enter rows with null value in a foreign key constrained column.   I
ran into this with my own schema, but then tried examples straight out of
PostgreSQL constraints documentation
http://www.postgresql.org/docs/9.4/static/ddl-constraints.html and was able
to reproduce it:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

-- INSERT order WITHOUT product_no
insert into orders(order_id, quantity) values(123,10)

Query returned successfully: one row affected, 13 ms execution time.

select * from orders;

returns the one row entered above

Despite the documentation (and general sql knowledge):

"Now it is impossible to create orders with non-NULL product_no entries that
do not appear in the products table.

We say that in this situation the orders table is the referencing table and
the products table is the referenced table. Similarly, there are referencing
and referenced columns."

irina.guberman@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      13665
> Logged by:          Irina
> Email address:      irina.guberman@gmail.com
> PostgreSQL version: 9.4.4
> Operating system:   Mac
> Description:
>
> I can enter rows with null value in a foreign key constrained column.

Yes.  This is not a bug.  If you want to prohibit NULL values, you need
to declare the column as NOT NULL.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Oct 7, 2015 at 12:42 AM,  <irina.guberman@gmail.com> wrote:
> The following bug has been logged on the website:
...
> I can enter rows with null value in a foreign key constrained column.   I
> ran into this with my own schema, but then tried examples straight out of
...
> Despite the documentation (and general sql knowledge):
>
> "Now it is impossible to create orders with non-NULL product_no entries that
> do not appear in the products table.

It's already been answered as not a bug, I just wanted to explain a
bit more. It seems you are misinterpreting the sentence. It does means
that the foreign key must either be null or appear in the products
table ( it cannot be null as it references a primary key or similar, a
non null unique column ). In fact you have a clause for foreign keys
"on delete set null" which would insert nulls. It says 'create orders
with non-null product_no', so the sentence does not apply when you
'create orders with NULL product_no', it canbe possible or impossible.

If what it models does not apply in your case ( as you may not accept
an order without a product ) you need to set a not null constraint in
the column besides the foreign key. But in some cases it does make
sense. I have a system where a customer table has a billing_account
associated, with a foreign key, but it can be null because customers
in a testing/preview phase are not billed and do not have a billing
account ( the field is null ), but the ones who have a billing account
must have it reference a valid key ( so the field is a foreign key ).

Francisco Olarte.