create a single check constraint across multiple columns guaranteeing only one is used
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
As Solution Using a Check Constraint with NVL2
Far too often I find developers and DBAs, especially those not trained in design and architecture thinking horizontally rather than vertically.

I just recently completed a 6 month project at a company where the basic application design was created in the 1970s by people with the punchcard mentallity. Every new datum seemed to cry out to them for another column. So a table like this was common.
CREATE TABLE report_views (
report_id     VARCHAR2(2),
view_name     VARCHAR2(30),
daily_rpt     VARCHAR2(1),
weekly_rpt    VARCHAR2(1),
monthly_rpt   VARCHAR2(1),
quarterly_rpt VARCHAR2(1),
annual_rpt    VARCHAR2(1));

desc report_views
Which made me want to rm -rf the whole thing. But professionalism sometimes means expressing one's feelings with profound silence.

What made it far worse, of course, ws that each view could be used with one and only one report so the problem became how to allow this:
INSERT INTO report_views
(report_id, view_name, daily_rpt)
VALUES
(1, 'IO_DAILY','X');

SELECT * FROM report_views;
And prevent this:
UPDATE report_views
SET daily_rpt = 'X', weekly_rpt = 'X', annual_rpt = 'X';

SELECT * FROM report_views;
A better design would, of course, have used only three columns to accomplish the goal but this was a legacy application and fixing the problem was not a politically acceptable option.

So the issue become one of enforcing the business rule that a view could be used in one, and only one, report.

The option that jumped out at everyone was to use a table trigger. Something like this:
CREATE OR REPLACE TRIGGER awful_trigger
BEFORE INSERT OR UPDATE
ON report_views
FOR EACH ROW
DECLARE
 x VARCHAR2(5);
BEGIN
  IF LENGTH(:NEW.daily_rpt ||
            :NEW.weekly_rpt ||
            :NEW.monthly_rpt ||
            :NEW.quarterly_rpt ||
            :NEW.annual_rpt) <> 1 THEN
    RAISE_APPLICATION_ERROR(-20101, 'Too many flags');
  END IF;
END awful_trigger;
/

TRUNCATE TABLE report_views;

INSERT INTO report_views
(report_id, view_name, daily_rpt)
VALUES
(1, 'IO_DAILY','X');

SELECT * FROM report_views;

UPDATE report_views
SET quarterly_rpt = 'X';

SELECT * FROM report_views;

UPDATE report_views
SET daily_rpt = NULL, quarterly_rpt = 'X';

SELECT * FROM report_views;
Well it does work. But it is both ugly and inefficient. Lets try it using a check constraint instead.
DROP TRIGGER awful_trigger;

ALTER TABLE report_views
ADD CONSTRAINT
cc_report_views_flags_unique
CHECK (NVL2(daily_rpt,1,0) + NVL2(weekly_rpt,1,0) + NVL2(monthly_rpt,1,0) +
NVL2(quarterly_rpt,1,0) + NVL2(annual_rpt,1,0) = 1);

SELECT * FROM report_views;

UPDATE report_views
SET daily_rpt = 'X';

SELECT * FROM report_views;

UPDATE report_views
SET daily_rpt = 'X', quarterly_rpt = NULL;

SELECT * FROM report_views;
That is much better and substantially faster. Lets look at the number doing transactions that don't raise an exception.
-- drop the check constraint
ALTER TABLE report_views DROP CONSTRAINT cc_report_views_flags_unique;

-- rebuild the trigger
CREATE OR REPLACE TRIGGER awful_trigger
BEFORE INSERT OR UPDATE
ON report_views
FOR EACH ROW
DECLARE
 x VARCHAR2(5);
BEGIN
  IF LENGTH(:NEW.daily_rpt ||
            :NEW.weekly_rpt ||
            :NEW.monthly_rpt ||
            :NEW.quarterly_rpt ||
            :NEW.annual_rpt) <> 1 THEN
    RAISE_APPLICATION_ERROR(-20101, 'Too many flags');
  END IF;
END awful_trigger;
/

set timing on

BEGIN
  FOR i IN 1 .. 100000 LOOP
    UPDATE report_views
    SET annual_rpt = NULL;
  END LOOP;
END;
/

-- drop the trigger and recreate the check constraint
DROP TRIGGER awful_trigger;

ALTER TABLE report_views
ADD CONSTRAINT cc_report_views_flags_unique
CHECK (NVL2(daily_rpt,1,0) + NVL2(weekly_rpt,1,0) + NVL2(monthly_rpt,1,0) +
NVL2(quarterly_rpt,1,0) + NVL2(annual_rpt,1,0) = 1);

-- and run the test again
BEGIN
  FOR i IN 1 .. 100000 LOOP
    UPDATE report_views
    SET annual_rpt = NULL;
  END LOOP;
END;
/

The following are the result from five test runs
  Run 1 Run 2 Run 3 Run 4 Run 5
Trigger 00:00:09.79 00:00:09.64 00:00:09.39 00:00:09.61 00:00:09:28
Check Constraint 00:00:06.81 00:00:07.78 00:00:07.37 00:00:07.67 00:00:07.77
 
Related Topics
Constraints
Indexes
Tables
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx