2

I'm just learning SQL and I can't seem to find out how to do this.

The table is (id, subject, year, grade). I'm supposed to reject any inserts or updates for a student if he has scored an F before for the same subject i.e. students are not allowed to retake a subject if they have failed in it before.

I'm supposed to use a trigger function but I'm confused about how to get started. A nudge to the right direction would be appreciated.

  • 1
    What happens if they get an B, and retake and get an F? Should that get inserted? – Evan Carroll Oct 1 '17 at 1:59
1

With an Exclusion Constraint and a UNIQUE Partial Index

So long as you don't care about people that pass retaking and later failing you can easily do this without even using a trigger using a . It's a bit clever, but it's pretty efficient.

CREATE TABLE foo (
  id         serial    PRIMARY KEY,
  student_id int,
  subject    text,
  year       smallint,
  grade      "char"    NOT NULL
);

CREATE UNIQUE INDEX no_double_fail
  ON foo ( student_id, subject ) WHERE upper(grade)='F';

ALTER TABLE foo
  ADD CONSTRAINT fail_once_fail_always
  EXCLUDE USING gist (
    student_id WITH =,
    (CASE WHEN upper(grade) = 'F' THEN 0::smallint ELSE 1::smallint END) WITH <>
  );

Test suite.

-- fails error msg below
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (1,'Art',2000,'F'),
  (1,'Art',2001,'A');

ERROR:  conflicting key value violates exclusion constraint "fail_once_fail_always"
DETAIL:  Key (student_id, (
CASE
    WHEN upper(grade::text) = 'F'::text THEN 0::smallint
    ELSE 1::smallint
END))=(1, 1) conflicts with existing key (student_id, (
CASE
    WHEN upper(grade::text) = 'F'::text THEN 0::smallint
    ELSE 1::smallint
END))=(1, 0).

-- works
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (2,'Math',2000,'B'),
  (2,'Math',2001,'A');

-- fails error msg below
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (3,'Science',2000,'F'),
  (3,'Science',2001,'F');

ERROR:  duplicate key value violates unique constraint "no_double_fail"
DETAIL:  Key (student_id, subject)=(3, Science) already exists.

If you care about it, to compliment the condition to protect against people that pass retaking and later failing you'll need the ability to do a non-commutative exclusion constraint. I'm not sure that's even possible. This may force you to use a trigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.