Using a trigger function to constrain values according to those already in the table (postgresql)
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 exclusion-constraint. 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 &lt;&gt;
  );


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.