How to set foreign key field field to null?

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
TheFastCoder
Posts: 10
Joined: Mon 18 Jun 2018 06:40

How to set foreign key field field to null?

Post by TheFastCoder » Mon 18 Jun 2018 06:58

I use a TIBCTable with a Firebird DB Table. The Table has a Field MyFieldX which has a foreign key reference to another table. This field can be null!

In TIBCTable.OnBeforePost I have this code:

Code: Select all

  if DataSet.FieldByName('MyFieldX').asString = '' then
    DataSet.FieldByName('MyFieldX').Clear;

Now I want to insert a new record:

This works:

Code: Select all

AIBCTable.Insert;
AIBCTable.FieldByName('Field1').asString := 'Test';
AIBCTable.FieldByName('MyFieldX').asString := 'X34B';
AIBCTable.Post;
This doesn't work:

Code: Select all

AIBCTable.Insert;
AIBCTable.FieldByName('Field1').asString := 'Test';
AIBCTable.Post;
It gives this error:

Code: Select all

violation of FOREIGN KEY constraint "FK_EXAMPLE" on table "MyTable"
Foreign key reference target does not exist
Problematic key value is ("MyFieldX" = '').
Why is MyFieldX an empty string? I thougt DataSet.FieldByName('MyFieldX').Clear would set it to null. How to set this Field to null? It must be null because this record should not have a connection to the other table.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to set foreign key field field to null?

Post by ViktorV » Mon 18 Jun 2018 07:46

Unfortunately, we could not reproduce the specified behavior.
Please make sure that you are using the latest version of IBDAC 6.1.7.
If this is so, in order for us to be able to give you a detailed answer, we need a sample demonstrating the behavior you mentioned. Therefore, please, compose a small sample demonstrating the described behavior and send it to us using the contact form https://devart.com/company/contactform.html including database objects creating scripts.

Post Reply