Only 1 of 3 columns per row can ever be populated!

[Bruce Pullen]

Thanks to denormalised developers, I have a table where only one
column of three can ever be populated per row. I feel
a) BEFORE INSERT/UPDATE triggers would have an unacceptable
performance impact (this table will be accessed heavily).
b) creating three views would be clumsy.
c) restricting inserts/updates using the application is far too much
to ask.

Does anyone have any bright ideas?

Thanks
Bruce

[Blair Adamache]

It would be easier to give advice if saw the DDL and some sample rows
for the table. Could you define all three columns with default values
and somehow define a constraint to enforce the rules you want?

Bruce Pullen wrote:

Thanks to denormalised developers, I have a table where only one
column of three can ever be populated per row. I feel
a) BEFORE INSERT/UPDATE triggers would have an unacceptable
performance impact (this table will be accessed heavily).
b) creating three views would be clumsy.
c) restricting inserts/updates using the application is far too much
to ask.

Does anyone have any bright ideas?

Thanks
Bruce

[Serge Rielau]

Bruce,

Is this dynamic SQL? As long as you exploit dynamic statement cache and or
use static SQL I don't see how a well written before trigger with a SIGNAL
statement can have a significant negative performance impact. The runtime
impact should be the evaluation time for the condition.
Keep in mind that DB2 for LUW uses inline SQL PL for triggers. Can't get any
faster than that.

Cheers
Serge

[Knut Stolze]

Bruce Pullen <br**********@h otmail.com> wrote:

Thanks to denormalised developers, I have a table where only one
column of three can ever be populated per row. I feel
a) BEFORE INSERT/UPDATE triggers would have an unacceptable
performance impact (this table will be accessed heavily).
b) creating three views would be clumsy.
c) restricting inserts/updates using the application is far too much
to ask.

What's the behavior that you want to have if 2 (or 3) of these columns are
to be populated? If you want to return an error, then a simple check
constraint on the table could be sufficient:

CHECK ( col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL OR
col1 IS NULL AND col2 IS NOT NULL AND col3 IS NULL OR
col1 IS NULL AND col2 IS NULL AND col3 IS NOT NULL )

If you want to handle the situation in the database system, then you would
need some more logic and triggers could be the way to go.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Bruce Pullen]

After proposing a check constraint and a trigger, we're going for the
check constraint (I thought it wouldn't be possible to enforce this
logic using check constraints so thank you).

Thank you.
Bruce