Can we have two primary keys in one Foreign key field?

Question

My db hierarchy is like Group -> Branch -> department. Employee and Roles are different table ,tbl_Employee having FK groupid,branchid, departmentid and RoleId. If I assign an employee as a '1' which is a groupHead according to Roles table then I have to provide this employee's branchId and depId as well. But all Branches of the particular group comes under him . So I was thinking of writing all the branchIds which comes under him. How should I proceed ?

Answer 1

You can build a structure like this:

create table Groups (
    GroupID char(2) not null primary key
)
create table Branches (
    GroupID char(2) not null foreign key references Groups (GroupID),
    BranchID char(2) not null primary key,
    constraint UQ_Branch_XRef UNIQUE (GroupID,BranchID)
)
create table Departments (
    GroupID char(2) not null foreign key references Groups (GroupID),
    BranchID char(2) not null foreign key references Branches (BranchID),
    DepartmentID char(2) not null primary key,
    constraint UQ_Department_XRef UNIQUE (GroupID,BranchID,DepartmentID),
    constraint FK_Department_Branch_XRef FOREIGN KEY (GroupID,BranchID)
        references Branches (GroupID,BranchID)
)
create table Employees (
    EmployeeID char(2) not null primary key,
    GroupID char(2) not null references Groups (GroupID),
    BranchID char(2) null references Branches (BranchID),
    DepartmentID char(2) null references Departments (DepartmentID),
    RoleID int not null,
    constraint FK_Employee_Branch_XRef FOREIGN KEY (GroupID,BranchID)
        references Branches (GroupID,BranchID),
    constraint FK_Employee_Department_XRef FOREIGN KEY (GroupID,BranchID,
                                                        DepartmentID) 
        references Departments (GroupID,BranchID,DepartmentID),
    constraint CK_AppropriateRole CHECK (
        (RoleID = 1 and BranchID is null and DepartmentID is null) or
        (RoleID = 2 and BranchID is not null and DepartmentID is null) or
        (RoleID = 3 and BranchID is not null and DepartmentID is not null)
    )
)

And then populate it like this:

insert into Groups (GroupID) values ('aa'),('bb')
insert into Branches (GroupID,BranchID) values
('aa','cc'),('aa','dd'),('bb','ee'),('bb','ff')
insert into Departments (GroupID,BranchID,DepartmentID) values
('aa','cc','gg'),
('aa','cc','hh'),
('aa','dd','ii'),
('bb','ee','jj'),
('bb','ff','kk'),
('bb','ff','ll')

And insert some valid employees

insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values
('mm','aa',null,null,1),
('nn','aa','dd',null,2),
('oo','bb','ee','jj',3)

But then you can't insert invalid rows:

insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values
('pp','bb',null,null,3)
insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values
('qq','aa','cc','hh',1)
insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values
('rr','aa','cc','ii',3)

And then it's implied that 'mm' is attached to group 'aa' and all branches and departments under it, 'nn' is attached to branch 'dd' of group 'aa' and all departments under that, and 'oo' is in department 'jj' of branch 'ee' of group 'bb'.

---

Whenever you store a NULL in (a column of) a foreign key, in SQL Server, then SQL Server doesn't enforce that foreign key.

---

The Constraints

There are 16 constraints in the 4 above tables, and most of them serve an important purpose. I'll ignore the primary keys since they're just fulfilling their usual role.

create table Branches (
    GroupID char(2) not null foreign key references Groups (GroupID),

Just a plain foreign key. Nothing special yet

    ...
    constraint UQ_Branch_XRef UNIQUE (GroupID,BranchID)

We need this unique constraint so that foreign keys can reference this table by both columns, so that we can cross-check that where a BranchID is used, it's used with the correct GroupID.

)
create table Departments (
    GroupID char(2) not null foreign key references Groups (GroupID),
    BranchID char(2) not null foreign key references Branches (BranchID),

The above two foreign key references aren't strictly necessary. The other FK defined below on this table will also ensure that the values are valid. But they are the "natural" FKs that most people would define. Whether you keep them or not is a matter of preference and possibly performance.

    ...
    constraint UQ_Department_XRef UNIQUE (GroupID,BranchID,DepartmentID),

This fulfils a similar role to UQ_Branch_XRef above, allowing FKs to reference this table by all 3 columns to ensure that all of the values are consistent.

    constraint FK_Department_Branch_XRef FOREIGN KEY (GroupID,BranchID)
        references Branches (GroupID,BranchID)

And this is the first FK that makes use of UQ_Branch_XRef, ensuring that our GroupID column is appropriate for our chosen BranchID.

)

create table Employees (
    ...
    GroupID char(2) not null references Groups (GroupID),

This FK constraint is important if both BranchID and DepartmentID are null - it's our only FK constraint that actually applies in such a circumstance.

    BranchID char(2) null references Branches (BranchID),
    DepartmentID char(2) null references Departments (DepartmentID),

These 2 FKs are technically redundant, given other FKs declared below, but they're the "natural" FKs.

    ...
    constraint FK_Employee_Branch_XRef FOREIGN KEY (GroupID,BranchID)
        references Branches (GroupID,BranchID),

This ensures that our GroupID and BranchID are correct. This FK is only important if DepartmentID is null, since in such a circumstance, the next FK won't apply a constraint

    constraint FK_Employee_Department_XRef FOREIGN KEY (GroupID,BranchID,
                                                        DepartmentID) 
        references Departments (GroupID,BranchID,DepartmentID),

This is the constraint that ensures all 3 of the named columns match up with the entry in the Departments table.

    constraint CK_AppropriateRole CHECK (
        (RoleID = 1 and BranchID is null and DepartmentID is null) or
        (RoleID = 2 and BranchID is not null and DepartmentID is null) or
        (RoleID = 3 and BranchID is not null and DepartmentID is not null)
    )

And finally this is the constraint that applies some business rules about what roles should have which columns filled in.

)