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."
