0

I am trying to use trigger (as suggested here by Bohemian) to constrain how many yes/no will occur in my table. I want at most one yes in the isTeamLead column per unique item in the project column but can have as many no as necessary. For no, it seems the best I can do is use a workaround where I can have something like no1, no2, no3, etc. My code successfully inserts the yes rows but throws errors on the no rows. 

DROP TABLE team CASCADE CONSTRAINTS PURGE ;
create table team (
  name varchar2(10) NOT NULL UNIQUE,
  project varchar2(10),
  isTeamLead char(10) check (isTeamLead IN ('No', 'Yes'))
);

create unique index only_one_yes_per_project on team(project, isTeamLead);

DROP SEQUENCE test1_seq; 
create SEQUENCE test1_seq
START WITH 1
INCREMENT BY 1;

set define off;
set serveroutput on format wrapped;
CREATE OR REPLACE TRIGGER insert_yesno
  BEFORE INSERT ON team
  FOR EACH ROW
BEGIN
  IF (:new.isTeamLead = 'No') THEN 
    DBMS_OUTPUT.put_line(:new.isTeamLead);
    :new.isTeamLead := CONCAT('No', test1_seq.nextval);
    DBMS_OUTPUT.put_line(:new.isTeamLead);
    INSERT INTO team VALUES
      (:new.name, :new.project, :new.isTeamLead);
  END IF; 
END insert_yesno;
/

insert into team values ('member1', 'project1', 'Yes');
insert into team values ('member2', 'project1', 'No');
insert into team values ('member3', 'project1', 'No');
insert into team values ('member4', 'project2', 'No');
insert into team values ('member5', 'project2', 'Yes');
insert into team values ('member6', 'project2', 'No');

select * from team;

Here's a snapshot of the error report:

Error starting at line : 244 in command -
insert into team values ('member6', 'project2', 'No')
Error report -
SQL Error: ORA-02290: check constraint (SEDEH.SYS_C0012563) violated
ORA-06512: at "SEDEH.INSERT_YESNO", line 6
ORA-04088: error during execution of trigger 'SEDEH.INSERT_YESNO'
02290. 00000 -  "check constraint (%s.%s) violated"
*Cause:    The values being inserted do not satisfy the named check

Please let me know if any thoughts. Thanks.

Running Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Community
  • 1
  • 1
sedeh
  • 7,083
  • 6
  • 48
  • 65
  • What values does the check constraint allow? You're trying to insert 'No' - if the check constraint is set up to allow 'yes' and 'no' then it won't allow 'No'. – Bob Jarvis - Слава Україні May 08 '15 at 00:08
  • Can you post a test case that reproduces the problem? You say that you're using a trigger but there is nothing in your example that shows a trigger being used. A check constraint is failing but we have no idea how that constraint is defined. – Justin Cave May 08 '15 at 00:13
  • @JustinCave I just modified and included a link to the [code](http://ideone.com/s2wFLw). I thought I had included it. Just noticed that if I remove `if/else` logic and used `WHEN (:new.isTeamLead = 'No')`, I got 2 of the `no` row. Interesting. – sedeh May 08 '15 at 00:20
  • Your `check` constraint doesn't make sense-- you can't simultaneously constrain `isTeamLead` to be either `Yes` or `No` and then try to insert a value `No12345` into the column. Your trigger should also be modifying the `:new` pseudorecord rather than trying to `insert` data into the `team` table (which, if it worked, would create an infinite loop). But it looks like you really want a function based index instead. – Justin Cave May 08 '15 at 00:25
  • @JustinCave Oops, should have said something like `check (isTeamLead = 'Yes' or isTeamLead like 'No%')`. Also great point re infinite loop. – sedeh May 08 '15 at 00:31

1 Answers1

2

Why not just add a unique function-based index? The following should constrain the column to have only one team lead per project:

create unique index idx_team_isTeamLead_yes on
    team(case when isTeamLead = 'yes' then project else NULL end);

This takes advantage of Oracle's ignoring rows where all the index columns are NULL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Great! I get `SQL Error: ORA-00942: table or view does not exist` on the modified [code](http://ideone.com/s2wFLw) but everything is inserted fine. Just curious if u ran into the same error. Thanks. – sedeh May 08 '15 at 01:08