Subject Re: [firebird-support] Foreign key violation, where there isn't
Author Helen Borrie
At 12:23 AM 28/05/2008, you wrote:
>I have a problem:
>I have to tables, one has a PK, the other has a FK for the previous
>table's PK.
>But then, when I try to post new record, it sais I have a violation of
>Foreign key on table. It is strange, because I tried it by hand, and
>with IBExpert's help to choose the proper record from the primary table.

Your problem is that IBExpert is providing some client-side facilities that hide the real requirements of relational databases and can lull you into the belief that IBExpert behaviour is database behaviour. Firebird itself does not "help you choose the proper record from the primary table".

>Even when I tried it without foreign key, I was able to insert the
>record, but then, it dropped me an error creating the foreign key.

Referential integrity rules require that a dependent record's FK value exists in the parent. Anything else and you get a FK violation. That is how RI works.

>Is there something I don't know about foreign keys, or is there
>something FB doesn't know about my foreign keys? :-)

Just keep your eyes open when using these "tools for idiots". Never assume that the handy little tricks available when using these tools reflect how the database engine really works.

There is one condition where a dependent record in a FK relationship is allowed to have NULL in the FK: this is when the FK was defined with the action ON DELETE SET NULL. In that case, it will be legal to delete the parent record. The child records will then have their FK set to NULL and become orphans. However, a FK constraint that is defined with action still does not allow you to *create* child records with a NULL FK.

Another thing to watch is that you do not define a FK relationship and then proceed to insert child rows without first COMMITTING the DDL for the constraint. People sometimes fall into this trap when processing scripts that mix DDL and DML. This is a good way to introduce logical corruption into your database. ;-)