From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | irina(dot)guberman(at)gmail(dot)com |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #13665: Foreign Key constraint doesn't work |
Date: | 2015-10-07 10:36:45 |
Message-ID: | CA+bJJbzu6FVNW7gsz=P0yTAVBRM-=s=TT_-8bHYJCXZqoMfZqg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Wed, Oct 7, 2015 at 12:42 AM, <irina(dot)guberman(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2015-10-07 12:41:17 | Re: BUG #13660: serializable snapshotting hangs |
Previous Message | Alvaro Herrera | 2015-10-07 02:24:02 | Re: BUG #13665: Foreign Key constraint doesn't work |