0

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?

1

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)
)
1
  • 1
    I think this is close to what I need, but I don't have ApplicationId on the SecurityPrincipal table, and I have multiple PrincipalTypes is a table linked to Application, but I can use the same technique using composite foreign keys. Paying 8 bytes for each row should be acceptable. Many, many thanks for the pointer! – A. Chiesa Feb 21 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.