4

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.

  • What if you put the region FK on country instead of User? The user would pick a country which would automatically belong to the correct region. – datagod May 19 '16 at 19:32
  • @datagod The problem is the user can choose to only provide region, which means country would be NULL. I suppose you could make a null entry in tCountry for every entry in tRegion but that seems excessive. – David Starkey May 19 '16 at 19:38
7

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
|improve this answer|||||
  • I red quite some documentation on sites about databases and design and this is the first time i see it was even possible. Isn't this not really widely known or did i just no got on the rights sites ? – Walfrat May 20 '16 at 7:17
  • @Walfrat Most probably such sites do exist, but i have no link at hand. And Any superset of PK is UNIQUE is just one of the Relational model facts. Applying it to your data comes rather from practice. Note it will cost you extra index. – Serg May 20 '16 at 7:42
  • Yes it is, but the part i didn't know was that you can apply foreign keys to more than one field, i saw lot of example about primary composite keys, unique composite keys, index on multiple columns, but never foreign key on multiple columns, so it never crossed my mind i could do it. Of course since you need 3 foreign key to handle special case, this cost a bit more when you modify data. I already applied it to my model i'm working on. – Walfrat May 20 '16 at 7:49
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.

|improve this answer|||||
2

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
;
|improve this answer|||||

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.