Re: Syntax question about returning value from an insert

> 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(dot)klaver(at)aklaver(dot)com
>
>