Trigger/Constraint to restrict inserts or updates

Question

I have a table which looks like

MyTable (
Student ID, 
Exam)

I want to place a trigger/constraint which describes:

A student ID can appear many times and does not have to be unique. However, the pair (Student ID, "French") can only appear once. So each student can only have one "French" entry. French is hardcoded

ID   Exam

0001 German  
0001 History  
0001 French  
0001 French <-- This insert should fail.

Attemting to update the "German" field to French should also fail

So far I've tried

CREATE OR REPLACE TRIGGER MyTrigger BEFORE INSERT OR UPDATE ON MyTable  
FOR EACH ROW
DECLARE
    rowsCount INTEGER;
BEGIN 
    select  count(*)
    INTO rowsCount
    from    MyTable  sc
    where SC.SC_TYPE = 'FRENCH' and :new.StudentID = sc.StudentID;
IF  rowsCount > 1    
THEN
     raise_application_error('-20098','You cannot have more than one French record per student.');
END IF;
end;

This is throwing a Mutate error though. Anyway, this is related to integrity, so I'm sure a constraint would be better, but would appreciate some advice on how to accomplish this.

So the restriction on `French` is specific, and they should be allowed to have multiple rows with `German`? — Damien_The_Unbeliever, Feb 06 '14 at 14:34

score 3 · Accepted Answer · edited May 23 '17 at 10:31

3

Yes, French can only appear once per student, but everything else can appear multiple times.

I'm thinking perhaps this is best solved with a index? Similar to: Oracle SQL Constraint where clause

CREATE UNIQUE INDEX MyIndex ON MyTable(
  CASE WHEN Exam= 'French'
       THEN StudentID
       ELSE NULL
  END
);

edited May 23 '17 at 10:31

Community

1
1

answered Feb 06 '14 at 14:38

Pythonn00b

325
1
4
20

That won't work either. If you have the same exam taken multiple times by any student the second insert will fail. Even if you change the index to be `(StudentID, CASE WHEN EXAM='French' THEN StudentID ELSE NULL END)` it will still fail the second time a non-"French" language is inserted for a given student, at least under Oracle 11.1. [SQLFiddle here](http://sqlfiddle.com/#!4/f8cdd9/1) – Bob Jarvis - Слава Україні Feb 06 '14 at 15:05
1

Having just the case statement in the index definition ought to be ok. If you remove the student_id column from index in the sqlfiddle then everything's fine. – David Aldridge Feb 06 '14 at 15:54

score 1 · Answer 2 · answered Feb 06 '14 at 15:14

Because of the specialized nature of the restriction (only French is restricted) I don't think this can be done with a constraint (although I'd be glad to learn otherwise). One way you CAN solve it is to use a statement trigger:

CREATE OR REPLACE TRIGGER NO_DUPLICATE_FRENCH_TRIGGER
  BEFORE INSERT OR UPDATE 
  ON MY_TABLE
  -- NOTE: NO 'FOR EACH ROW', which means this is fired once for each 
  -- statement executed, rather than once for each row modified.
DECLARE
  nMax_count NUMBER;
BEGIN
  SELECT MAX(COUNT_VAL)
    INTO nMax_count
    FROM (SELECT STUDENTID, COUNT(*) AS COUNT_VAL
            FROM MY_TABLE
            WHERE EXAM = 'FRENCH'
            GROUP BY STUDENTID);

  IF nMax_count > 1 THEN
    RAISE SOME_EXCEPTION;
  END IF;
END NO_DUPLICATE_FRENCH_TRIGGER;

Statement triggers have the advantage that they're not subject to the "mutating table" issue as is a row trigger. However, this is a bit of a kluge, introduces a full table scan, and if the table is large may be a performance issue, but at least it's A solution.

Share and enjoy.

score 0 · Answer 3 · answered Feb 06 '14 at 16:30

I suggest you create 3 triggers and a package to handle this validation.

1 package with a PL/SQL table and procedures that will be called by your triggers.
1 before statement trigger to clear the PL/SQL table.
1 before (or after) each row trigger to add to the PL/SQL table the records you are inserting / updating.
1 after statement trigger to perform the validation, based on the PL/SQL table you have populated.

You can add to the PL/SQL table only the records with Exam = 'French', and check if the student already has a french exam on the database.

score 0 · Answer 4 · answered Feb 06 '14 at 21:58

0

How about this?

alter table MyTable add constraint std_exam unique (Student ID, Exam);

answered Feb 06 '14 at 21:58

c.Porter.fc

3
1

Trigger/Constraint to restrict inserts or updates

4 Answers4