對間接數據創建外鍵約束

我正在嘗試在某些包含感測器數據的數據庫設計中強制執行關係完整性。數據庫的相關部分：

CREATE TABLE logger (
   id INTEGER PRIMARY KEY
);

CREATE TABLE sensor (
   logger_id INTEGER REFERENCES Logger(id),
   logger_sensor_id SMALLINT,
   PRIMARY KEY (logger_id, logger_sensor_id)   
);

CREATE TABLE measurement (
   id BIGINT PRIMARY KEY,
   logger_id INTEGER REFERENCES logger(id)
)

CREATE TABLE sensor_measurement_data (
   measurement_id BIGINT REFERENCES measurement(id),
   logger_sensor_id REFERENCES sensor(logger_sensor_id) -- problem is here
)

由於logger_sensor_id在表中不是唯一的sensor，因此我無法在其中創建外鍵，sensor_measurement_data並且上述方法不起作用。我想在表中包含的logger_sensor_id和上創建一個外鍵。logger_id``measurement

是否可以創建這樣的“間接外鍵”約束？我想可以通過觸發器和檢查來確保參照完整性，但我想知道外鍵是否可以實現——它看起來對我來說不太容易出錯。如果不可能，是否有技術原因？

我目前正在使用 postgresql，但我肯定想知道其他系統是否能夠實現上述功能。

-- Logger LOG exists.
--
logger {LOG}
   PK {LOG}

-- Sensor number SNS# of logger LOG exists.
--
sensor {LOG, SNS#}
   PK {LOG, SNS#}

FK1 {LOG} REFERENCES logger {LOG}

-- Measurement batch MEA was
-- received from logger LOG at time TIM.
--
measurement {MEA, LOG, TIM}
        PK {MEA}
        AK {LOG, TIM}
        SK {MEA, LOG}

FK1 {LOG} REFERENCES logger {LOG}

-- Value VAL was received from sensor number SNS#
-- of logger LOG in measurement batch MEA.
--
sensor_meas {MEA, LOG, SNS#, VAL}
        PK {MEA, LOG, SNS#}

FK1 {LOG, SNS#} REFERENCES sensor {LOG, SNS#}

FK2 {MEA, LOG}  REFERENCES measurement {MEA, LOG}

筆記：

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename SNS# to SNS_NO.

• 為什麼measurement會有一個引用正常 int in 的 bigint logger？一個或另一個（可能只是一個 int）
• 如果 sensor_measurement_data 與測量值是 1:1，只需將數據添加到measurement. 為什麼這是兩個單獨的表？

建議的架構，

CREATE TABLE logger (
   logger_id  serial PRIMARY KEY
);


CREATE TABLE sensor (
   sensor_id    serial   PRIMARY KEY
   logger_id    int      REFERENCES logger,
);

CREATE TABLE measurement (
   measurement_id  serial  PRIMARY KEY,
   sensor_id       int     REFERENCES sensor,
   data            -- whatever
);

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