> On Dec 25, 2019, at 11:56 AM, Adrian Klaver < >> First of all, thanks to both of you for your fast response . >> Let me clarify. >> I have a table that records will be inserted into. Several of the columns >> in this table must be non NULL, and they are actually keys from other >> tables. Like >> Table t1 >> has a column like cost_category_key >> So if an INSERT to this table gets called with this column as a NULL, I am >> creating a function that will query for the default category, like >> SELECT cost_category_key from t2 where type = 'Misc' >> Now suppose that the default category has not yet been inserted in T2. I >> can easily detect this as the SELECT will return a NULL. So what I want to >> do is go ahead and insert this row. Once this is done, the correct default >> row will exist in T2, but I still need the (automatically assigned) key for >> this row to place in the NEW. structure for the function that is called On >> insert to t1, and checks to see if the value supplied for this key is in >> t2. >> Make more sense? > > No. It looks like you are trying to do a backwards FK. I would say your life would be a lot easier if you used FK's as intended e.g. have t2 be the parent table and INSERT the correct type/key combination there first before you INSERT into t1, as separate operations. As you script it out above you have to know what the the type/key is before you INSERT into t1 anyway. > > If you know today what those defaults are then load them today. That allows a standard FK from t1 to t2. Also streamlines adding new values (no code required). Your current plan is at risk of typos causing new bogus defaults. Are your multiple non-null columns each a separate domain referencing separate “t2”s? > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx > >
