RE: [firebird-support] Optional FK constraint on 0 (not NULL)

[venussof...@gmail.com [firebird-support]]

Thanks András.  I see it now, the constraint is on the field that mirrors the 
non zero values and has NULL instead of zeros, yet I can still use my present 
field the way it always works in the software, that's great

Kind regards
Bhavbhuti

RE: [firebird-support] Optional FK constraint on 0 (not NULL)

[Omacht András aoma...@mve.hu [firebird-support]]

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-supp...@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-supp...@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]

RE: [firebird-support] Optional FK constraint on 0 (not NULL)

[Omacht András aoma...@mve.hu [firebird-support]]

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-supp...@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]