BUG #13665: Foreign Key constraint doesn't work

From: irina(dot)guberman(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13665: Foreign Key constraint doesn't work
Date: 2015-10-06 22:42:12
Message-ID: 20151006224212.632.12147@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13665
Logged by: Irina
Email address: irina(dot)guberman(at)gmail(dot)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."

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2015-10-07 02:24:02 Re: BUG #13665: Foreign Key constraint doesn't work
Previous Message Chris Pacejo 2015-10-06 21:47:08 Re: BUG #13660: serializable snapshotting hangs