在 SQL 數據庫中，如何添加依賴於外鍵連結表中的值的唯一約束？

例如，我有 2 個表：

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

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

如圖所示，設備表將其位置引用到物理位置表。

我需要做的是強制設備表中logical_address 列的唯一性，但僅在與其位置記錄關聯的區域內。

例如，考慮這些記錄：

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

我只是不知道如何在外鍵引用表中為 UNIQUE 約束引用此列。或者，我不確定如何構造我的數據以在沒有此類問題的情況下維護此要求。

我的一個想法是創建另一個具有地址和區域列的logical_address 表，對兩者都有唯一約束，並簡單地將設備邏輯地址引用到具有唯一約束的這些條目，但這在另一個領域引入了相同的問題。然後，設備的位置可能與邏輯地址的區域不匹配。

有沒有辦法使用唯一約束或重組我的表來強制執行此操作，或者我需要更高級的東西嗎？

一個常見的技巧是向物理位置添加唯一約束：

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)
);

現在可以在外鍵中引用它（保證使用相同的位置，區域：

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);

缺點是您必須將區域添加到設備：

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

唯一約束防止重複：

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

UNIQUE constraint failed: devices.logical_address, devices.district

正如@ypercube 在評論中指出的那樣，需要啟用外鍵：

PRAGMA foreign_keys = ON;

如果我們試圖通過使用另一個區域來欺騙唯一約束，外鍵會報錯：

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

FOREIGN KEY constraint failed

小提琴

引用自：https://dba.stackexchange.com/questions/313913