Sorry, two little corrections:

3. create a before insert or update trigger which fill up this field like this: new.master_id_fk = nullif(new.master_id, 0);

András

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, March 28, 2018 9:44 AM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Optional FK constraint on 0 (not NULL)



Hi Bhavbhuti,

I can suggest you only a workaround….

1. your current field name is: MASTER_ID
2. add a new field, e.g.: MASTER_ID_FK
3. create a before insert and update trigger which fill up this field like this: new.master_id_fk = nullif(master_id, 0);
4. create the foreign key on MASTER_ID_FK field.

András

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups..com]
Sent: Wednesday, March 28, 2018 8:54 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Optional FK constraint on 0 (not NULL)



Hi all

I have a transaction table that has an integer FK field. This FK field can be either a 0 (no master record selected) or an integer that is a FK value from the master PKs. If I put in a FK Constraint then it will not allow me a 0 in the transaction table's FK field. I know NULL is allowed but for the present I can't use NULL in the transaction table's FK field as it might impact other operations.



Is there an intermediate way to constraint only if a value > 0 is present in the transaction table's FK field. I need to stop a master record from deleting in case there is a reference.



Please advise



Kind regards

Bhavbhuti





__________ Information from ESET Mail Security, version of virus signature database 17128 (20180328) __________

The message was checked by ESET Mail Security.
http://www.eset.com

[Non-text portions of this message have been removed]



__________ Information from ESET Mail Security, version of virus signature database 17128 (20180328) __________

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]