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

I have a table with the primary key (this is called "person"), and another table that gives context to it This "grade", as in the student grade).

The field "grade P." in the table "grade" , Which is a foreign key of "person.personid". The field of "person" is said to be the "person" type, as well as (the simplicity of "student" or "teacher" with the values ​​of potential values), and only grade of students.

How do I create a database? Reject any entries / updates that put a "non-student (i.e. teacher") in the "grade .Personid" field.

I am currently working with SQL Server 2008, but will also be interested in answering other platforms.

  [grade] [person] [--------] [--------] [graded] [personality] and lieutenant; -FK- [Personality] [Type] [Data] [Name]  

ps I know about obstacles on the boundaries of schema but in fact it does not like because Whenever anyone changes the tables they trust, they break them.

define INSTEAD OF triggers in the case of SQL Server The desired logic can be applied, for example, for the MySQL server, you will need to define before trigger.

Update Trigger Example (SQL Server)

  [Grade] does not start before triggering on Instagram (if selected 1 From [person] where person = was inserted, grayscale and person type = 'student') RACROR ('type of invalid person', 10, 1); End; INSERT [Grade] SELECT includes field1, field2, ...; And for GO  

mysql, it should be create trigger ... before INSERT . Also, there is no analog of riserator in mysql, so you will need to generate an error to stop it from entering. Generally, I use INSERT not_existing_table (id) VALUES (1,2) to get a runtime error in the trigger body.