Maintaining integrity between 3 tables when reference could be through or direct

Question

Say we have the following 3 tables:

tUsers (ID, name, countryID, regionID)
------
1  |  Joe  |  1   |  1
2  |  Sue  | NULL |  2
3  |  Bob  |  2   |  NULL
4  |  Amy  | NULL |  NULL

tCountries (ID, name, regionID)
------
1  |  USA   |  1
2  |  China |  3

tRegions (ID, name)
------
1  |  North America
2  |  Central America
3  |  East Asia
4  |  Europe

Is there a way from the DB to prevent a user from having mismatched Country/Region while still allowing one or both to be NULL? For example, the query UPDATE tUsers SET regionID=4 WHERE ID = 3 would fail since Bob's country is China which is not in region Europe.

Answer 1

3 foreign keys version

create table tRegions (
  ID int not null primary key, 
  name  varchar(20)
); 

create table tCountries (
  ID int not null primary key, 
  name varchar(20), 
  regionID int, 
  --any superset of PK is undoubtly UNIQUE
  constraint cu1 unique (ID, regionID),
  foreign key (regionID) references tRegions(ID)
  );

create table tUsers (
 ID int not null primary key, 
 name varchar(20), 
 countryID int ,
 regionID int,
 foreign key (regionID) references tRegions(ID),
 foreign key (countryID) references tCountries(ID),
 foreign key (countryID,regionID) references tCountries(ID,regionID )
)

insert tRegions (ID, name)
values
(1,'North America'),
(2,'Central America'),
(3,'East Asia'),
(4,'Europe') 

insert tCountries(ID, name, regionId)
values
(1,'USA',1),
(2,'China',3)

insert tUsers (ID, name, countryID, regionId)
values
(1,'Joe', 1  ,1),
(2,'Sue',NULL,2),
(3,'Bob', 2  ,NULL),
(4,'Amy',NULL,NULL)

go
--OK
update tUsers set RegionId=3
where ID=3
select * from tUsers
go
--fails
update tUsers set CountryId=2
where ID=2
select * from tUsers

Answer 2

It is possible to ensure tUsers.regionID matches tCountries.regionID when tUsers.countryID is set if you agree to introduce some redundancy in the form of an additional unique constraint (and the index supporting it).

The idea is as follows:

• Make sure tCountries.ID and tRegions.ID are the PKs of their respective tables.

  ALTER TABLE
    dbo.tCountries
  ADD
    CONSTRAINT PK_tCountries
    PRIMARY KEY (ID)
  ;
  
  ALTER TABLE
    dbo.tRegions
  ADD
    CONSTRAINT PK_tRegions
    PRIMARY KEY (ID)
  ;
  

• Have tCountries.regionID reference tRegions.ID (not directly relevant to this solution, but just for completeness).

  ALTER TABLE
    dbo.tCountries
  ADD
    CONSTRAINT FK_tCountries_regionID
    FOREIGN KEY (regionID) REFERENCES tRegions (ID)
  ;
  

• Have tUsers.countryID reference tCountries.ID.

  ALTER TABLE
    dbo.tUsers
  ADD
    CONSTRAINT FK_tUsers_countryID
    FOREIGN KEY (countryID) REFERENCES tCountries (ID)
  ;
  

• Have tUsers.regionID reference tRegions.ID.

  ALTER TABLE
    dbo.tUsers
  ADD
    CONSTRAINT FK_tUsers_regionID
    FOREIGN KEY (regionID) REFERENCES tRegions (ID)
  ;
  

• Create a unique constraint on (ID, regionID) in tCountries.

  ALTER TABLE
    dbo.tCountries
  ADD
    CONSTRAINT UQ_tCountries_IDregionID
    UNIQUE (ID, regionID)
  ;
  

• Have (countryID, regionID) in tUsers reference (ID, regionID) in tCountries.

  ALTER TABLE
    dbo.tUsers
  ADD
    CONSTRAINT FK_tUsers_countryIDregionID
    FOREIGN KEY (countryID, regionID) REFERENCES tCountries (ID, regionID)
  ;
  

When either country or region is not set, the composite foreign key will not be checked because of the NULL(s) – similarly to how a single-column foreign key is not checked if it is null. If both columns are set, however, all three foreign keys will work, preventing the discrepancy in the regions between tUsers and tCountries.

Answer 3

Rather than making sure tUsers.regionID matches tCountries.regionID when tUsers.countryID is set, you could simply prevent both countryID and regionID from being set simultaneously on a row by adding a check constraint like this:

ALTER TABLE
  dbo.tUsers
ADD
  CONSTRAINT CK_CountryRegion
  CHECK (countryID IS NULL OR regionID IS NULL)
;

Now, if a country is set, you can derive the region through tCountries – either like this:

SELECT
  UserName = u.name,
  Country  = c.name,
  Region   = r.name,
FROM
  dbo.tUsers AS u
  LEFT JOIN dbo.tCountries AS c ON u.countryID = c.ID
  LEFT JOIN dbo.tRegions AS r ON r.ID IN (u.regionID, c.regionID)
;

or like this:

SELECT
  UserName = u.name,
  Country  = c.name,
  Region   = ISNULL(ru.name, rc.name),
FROM
  dbo.tUsers AS u
  LEFT JOIN dbo.tRegions AS ru ON ru.ID = u.regionID
  LEFT JOIN
    dbo.tCountries AS c
    INNER JOIN dbo.tRegions AS rc ON rc.ID = c.regionID
  ON u.countryID = c.ID
;