How to make a foreign key available only for reference to a subset of rows in the target table

Question

I have a table with a primary key (allows me to call her "person") and another table that refers to her (allows me to call her "grade", as in student grades).

The grade table has a grade.personid field, which is the foreign key for person.personid. Suppose a “person” has a field of “person.type” (a varchar with the possible meanings “student” or “teacher” for simplicity), and only students have grades.

How to make the database reject any insertions / updates that put the "personid" of a non-student (ie teacher) in the "grade.personid" field.

I am currently working with Sql Server 2008, but I will also be interested in answers to other platforms.

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

ps I know the limitations on schema-bound views, but I really don't like them because they break whenever someone modifies the tables they rely on.

Answer 1

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

The UPDATE . Trigger Example (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

mysql CREATE TRIGGER ... BEFORE INSERT. , mysql RAISEERROR, , . INSERT INTO not_existing_table(id) VALUES(1,2) .

Answer 2

:

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

FK personid + .type, .

Answer 3

, :

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

11, , " . ".

. RAISERROR (sic)