In a SQL database, how can I add a unique constraint that depends on a value in a foreign key linked table?

Question

As an example, I have 2 tables:

devices
-----------------
id (Primary Key)
logical_address
physical_location (Foreign Key (physical_locations.id))

physical_locations
------------------
id (Primary Key)
name
district

As shown, the devices table references its location to the physical locations table.

What I need to do is enforce uniqueness of the logical_address column in the devices table but ONLY within the district associated with its location record.

So for example, consider these records:

physical_locations
----------------------
id  name      district
LA   loc_A     1
LB   loc_B     1
LC   loc_C     2
LD   loc_D     2

devices
----------------------
id  logical_address    physical_location
1   100                LA                 <-----this is okay, same district as 2 but different address
2   201                LB                 <-----this is okay, same district as 1 but different address
3   100                LC                 <-----this is also okay, same address as 1 but different district 
4   100                LD                 <-----this is NOT OKAY, same address AND same district as 3

I just don't know how to reference this column in a foreign key referenced table for a UNIQUE constraint. Alternatively, I am not sure how to structure my data to maintain this requirement without such an issue.

One thought I had was to create another logical_address table with address and district columnns, with a unique constraint on both and simply reference device logical addresses to these entries with a unique constraint, but that introduces the same issue in another area. Then it is possible for a device to have a location with a district that does not match the district of the logical_address.

Is there a way to enforce this using a unique constraint or by restructuring my tables, or will I need something more advanced?

Answer 1

A common trick is to add a unique constraint to physical_locations:

create table physical_locations
( physical_location_id char(2) not null --  primary key
, name varchar(20) not null
, district int not null
, UNIQUE (district, physical_location_id)
);

Now it is possible to reference that in a foreign key (guarantees that the same location, district is used:

create table devices
( device_id int not null primary key
, logical_address int not null
, district int not null
, physical_location_id char(2) not null
,     foreign key (district, physical_location_id)
      references physical_locations (district, physical_location_id)
,     UNIQUE (logical_address, district)      
);

insert into physical_locations (physical_location_id, name, district)
values ('LA', 'la', 1)
     , ('LB', 'lb', 1)
     , ('LC', 'lc', 2)
     , ('LD', 'ld', 2);

Drawback is that you have to add district to devices:

insert into devices (device_id,logical_address, physical_location_id, district)
values (1, 100, 'LA', 1)
     , (2, 201, 'LB', 1)
     , (3, 100, 'LC', 2);

The unique constraint prevents duplicates:

insert into devices (device_id,logical_address, physical_location_id, district)
values (4, 100, 'LD', 2);

UNIQUE constraint failed: devices.logical_address, devices.district

As @ypercube noted in a comment, one needs to enable foreign keys with:

PRAGMA foreign_keys = ON;

If we try to fool the unique constraint by using another district, the foreign key will complain:

insert into devices (device_id,logical_address, physical_location_id, district)
values (4, 100, 'LD', 3);

FOREIGN KEY constraint failed

Fiddle