All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂