SQL Server - ensuring a complex relation on insert I have some related tables:
Application - stores a list of applications
ApplicationInstall - for each application, multiple installations are present (I have a foreign key ApplicationUid)
SecurityPrincipal - for each ApplicationInstall I have many principals (I have a foreign key ApplicationInstallUid).
I have also a table SecurityPrincipalType, linked to Application through a 1-to-many.
I would like to have a rule that guarantees that when I insert a record in SecurityPrincipal its Type comes from a SecurityPrincipalType linked to the correct Application.
I didn't rename the tables because I usually prefer to have names to think about. But I think the following scenario is equivalent:
Table A n..1 Table B 1..n Table C
And I want to enforce that a column in C contains only values coming from A, but linked via B.
I'm considering:

triggers
foreign keys?
application level checks (but I would prefer to have some check at the db level)

What should I do?
You can use a Foreign Key for this if you add a unique constraint on Application(Id,SecurityPrinicpalType), something like this:
--drop table if exists ApplicationInstallSecurityPrinicpal
--drop table if exists ApplicationInstall
--drop table if exists Application

go

create table Application
(
  Id int identity not null, 
  SecurityPrincipalType int,
  constraint pk_Apppliction
    primary key (Id),
  constraint ak_Application
    unique(Id,SecurityPrincipalType)
)

create table ApplicationInstall
(
  ApplicationId int not null,
  Id int identity not null,
  constraint pk_ApplicationInstall
    primary key (ApplicationId,Id),
  constraint fk_ApplicationInstall_Application
    foreign key (ApplicationID) 
    references Application(Id)
)


create table ApplicationInstallSecurityPrinicpal
(
  ApplicationId int not null,
  ApplicationInstallId int not null,
  Id int identity not null, 
  SecurityPrincipalType int,
  constraint pk_ApplicationInstallSecurityPrinicpal
    primary key (ApplicationId,ApplicationInstallId,Id),
  constraint fk_ApplicationInstallSecurityPrinicpal_ApplictionInstall
    foreign key (ApplicationId,ApplicationInstallId)
    references Application(ApplicationId,Id)
  constraint fk_ApplicationInstallSecurityPrinicpal_Appliction
    foreign key (ApplicationId,SecurityPrincipalType)
    references Application(Id,SecurityPrincipalType)
)