How can I make a foreign key only able to reference a subset of rows in the target table

Question

I have a table with a primary key (lets call it "person"), and another table that references it (lets call it "grade", as in student grades).

Table "grade" has field "grade.personid", which is a foreign key to "person.personid". Lets say "person" has field "person.type" as well (varchar with possible values of "student" or "teacher" for simplicity), and only students have grades.

How do I make the database reject any inserts/updates that put the "personid" of a non-student (i.e. teacher) in the "grade.personid" field.

I'm currently working with Sql Server 2008, but would be interested in answers for other platforms too.

                 [ grade  ]
[ person ]       [--------]
[--------]       [gradeid ]
[personid] <-FK- [personid]
[type    ]       [data    ]
[name    ]

p.s. I'm aware of constraints on schema bound views but don't really like them because they break whenever anyone modifies the tables they rely on.

Answer 1

try this:

                 [ grade  ]
[ person ]       [--------]
[--------]       [gradeid ]
[personid] <-FK- [personid]
[type    ] <-FK- [type    ]
[name    ]       [data    ]

put a single FK on personid+type and add a check constraint on grade.type to only allow student type.

Answer 2

In case of SQL Server desired logic can be implemented by defining INSTEAD OF trigger. For Mysql server, for instance, you will need to define BEFORE trigger.

UPDATE. Example of trigger(SQL server)

CREATE TRIGGER ON [grades] INSTEAD INSERT
AS
BEGIN
   IF NOT EXISTS(   
   SELECT 1 FROM [person] WHERE personid = inserted.person_id AND person.type  = 'student'
   )
   BEGIN
      RAISERROR ('Invalid person type', 10, 1);
   END;

   INSERT INTO [grades] SELECT field1, field2, ... FROM inserted;
END
GO

For mysql, it should be CREATE TRIGGER ... BEFORE INSERT. Also, mysql doesn't have an analogue of RAISEERROR, so you will need to generate an error to prevent inserting. Usually, I use INSERT INTO not_existing_table(id) VALUES(1,2) to get a runtime error in a trigger body.

Answer 3

And for the record, here's an anonymized version of what worked for me:

CREATE TRIGGER trigRestrictGradeToStudents
ON dbo.grade
FOR INSERT, UPDATE
AS
IF NOT EXISTS (
    SELECT person.personid
    FROM person
        INNER JOIN inserted ON person.personid = inserted.personid
    WHERE (type = 'student')        
)
BEGIN
    ROLLBACK TRANSACTION --prevent the insert/update from proceeding
    RAISERROR ('Only students may have grade records', 11, 1)
END

Severity set to 11 to make it show in the error message before "The transaction ended in the trigger. The batch has been aborted."

See also RAISERROR (sic)