Oracle SQL setting single unique character

Question

I am having trouble with creating a table in oracle SQL.

I was looking to have a column called DEFAULTRULE. Only 1 row in this column can be '1' at any given time. So for example

ID    DEFAULTRULE
1     0
2     0
3     1

If I was to update ID 2 to have default rule = 1 then it would either set ID 3 default rule to 0

or

throw an error (I don't really mind as long as there is only one '1')

I have tried to create triggers to do both but keep getting the

ORA-04091 table is mutating, trigger/function may not see it.

2 of my attempts:

CREATE OR REPLACE TRIGGER CHECKDEFAULTRULE
  BEFORE UPDATE ON BUSINESS_RULE
  FOR EACH ROW
DECLARE 
v_count NUMBER(1);
BEGIN 
  IF :NEW.DEFAULTRULE = 1 THEN
      SELECT COUNT(DEFAULTRULE)INTO v_count FROM BUSINESS_RULE WHERE DEFAULTRULE = 1;
      IF v_count != 0 THEN
       RAISE_APPLICATION_ERROR(-20000,'BUSINESS_RULE already has a default rule. Please set this to 0 and try again');
      END IF;  
   END IF;
END;

and

CREATE OR REPLACE TRIGGER CHECKDEFAULTRULE
BEFORE UPDATE ON BUSINESS_RULE
FOR EACH ROW
BEGIN 
IF :new.DEFAULTRULE = 1 THEN
   UPDATE BUSINESS_RULE
   SET    DEFAULTRULE = 0;
   WHERE  DEFAULTRULE = 1; 
END IF;
END checkDefaultForOne;

Would anyone be able to point me in the right direction? Thanks in advance. Jon

Answer 1

You don't need a trigger.
Use an expression based unique index:

CREATE UNIQUE INDEX someindex ON sometable( 
  CASE WHEN DEFAULTRULE = 1
       THEN 1
  END
  );

See this demo: http://sqlfiddle.com/#!4/2431a
It allows for insetring many DEFAULTRULE != 1, but only one DEFAULTRULE = 1
try to append additional INSERT INTO sometable VALUES( 1,1); in this demo and you will get an error.