4

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?

1 Answer 1

4

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

7
  • 2
    you need to enable foreign keys in SQLite with PRAGMA foreign_keys = ON;: dbfiddle.uk/… Jun 29 at 19:01
  • Ah, thanks. I was not aware of that
    – Lennart
    Jun 29 at 19:27
  • 1
    This could typically be solved with a before trigger, example at: dbfiddle.uk/… . But it appears as if before triggers can only be used to validate data in sqllite (unless ypercube has another pragma up his sleeves:-)
    – Lennart
    Jun 29 at 19:53
  • 1
    Another option is to validate the insert/update with triggers
    – Lennart
    Jun 29 at 19:56
  • 1
    You don't really need the trigger (other than for a more intuitive message), if you drop the trigger the same inserts will fail. What I meant is that you could use a validation trigger instead of duplicating district, adding unique constraints etc. Major drawback is that you only know that the rows after you created the trigger is validated, the other approach should guarantee that all existing and future data is valid.
    – Lennart
    Jun 30 at 20:57

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.