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

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.