CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
Awer Muller
Awer Muller

Reputation: 627

Referential integrity over table portion

I'm designing a database and can't figure out how to model referential integrity.

I have the following tables

CREATE TABLE Groups
(
GroupId INT PRIMARY KEY,
GroupName VARCHAR(50)
)

CREATE TABLE GroupMembers
(
GroupId INT NOT NULL,
MemberId INT NOT NULL,
MemberName VARCHAR(50),
CONSTRAINT pk_GroupMember PRIMARY KEY (GroupId, MemberId)
)

CREATE TABLE Missions
(
MissionId INT PRIMARY KEY,
GroupId INT NOT NULL,
MissionName VARCHAR(50)
)

CREATE TABLE MissionRollAssignments
(
MissionId INT NOT NULL,
MemberId INT NOT NULL,
MemberRoll VARCHAR(50)  --This will probably become RollId and move details to another table
)

Every mission will have assignments for some/all members of the corresponding group. There will be several missions associated with each group, but only one mission per group is active at a given time.

My question is:

Is it possible to enforce referenciay integrity for roll assignments such that only members of the corresponding group (given by the MissionId) are selected? I know I can filter this from the GUI, but I'd feel more comfortable if I could create a FK constraint from MissionRollAssignments to GroupMembers while considering the GroupId indicated in the Mission.

A second question would be if you guys think this is a good way to model my domain, or maybe I should try a different approach.

Thanks in advance for any help on this.

Best regards,
Awer

Upvotes: 0

Views: 124

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Is it possible to enforce referenciay integrity for roll assignments such that only members of the corresponding group (given by the MissionId) are selected?

Yes. You need to use identifying relationships to propagate the GroupId all the way down to the bottom of this "diamond-shaped" dependency, similar to this:

enter image description here

Note FK1 and FK2 in front of MissionRollAssignment.GroupId, indicating that foreign keys exist up the both "sides" of this "diamond-shaped" dependency.

As single active mission can be modeled as a foreign key in the opposite direction, in this case as Group {GroupId, ActiveMissionNo} that references the Mission primary key.

Such circular foreign key presents a "chicken-and-egg" problem on a DBMS that doesn't support deferred constraints (which SQL Server doesn't). However, you can just leave Group.ActiveMissionNo NULL-able, so a DBMS that enforces foreign keys in a MATCH SIMPLE fashion (which SQL Server does) will ignore the whole composite foreign key if just one of its fields is NULL. This will allow you to temporarily "disable" the foreign key and break the "chicken-and-egg" cycle when inserting new data.

Upvotes: 0

nvogel
nvogel

Reputation: 25526

You could put GroupId into MissionRollAssignments and then add two constraints as follows:

ALTER TABLE MissionRollAssignments
 ADD CONSTRAINT fk1 FOREIGN KEY (GroupId, Memberid)
 REFERENCES GroupMembers (GroupId, Memberid);

ALTER TABLE MissionRollAssignments
 ADD CONSTRAINT fk2 FOREIGN KEY (GroupId, MissionId)
 REFERENCES Missions (GroupId, MissionId);

To achieve this SQL Server first requires a (redundant) UNIQUE constraint on (GroupId, MissionId) in the Missions table. Other DBMSs are not so strict but SQL Server requires a FOREIGN KEY constraint to match exactly the columns of a uniqueness constraint.

Upvotes: 2

SAS
SAS

Reputation: 4035

You should use Foreign Keys to reinforce this, eg Mission.GroupId should refer to Group.GroupId.

Upvotes: 0

Related Questions