Create foreign key constraint on indirect data

Question

I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database:

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
)

Since logger_sensor_id is not unique in the sensor table, I can't create the foreign key in sensor_measurement_data and the above won't work. I want to create a foreign key on both the logger_sensor_id and the logger_id which is included in the measurement table.

Is it possible to create such an "indirect foreign key" constraint? I guess it's possible to ensure referential integrity through triggers and checks, but I'd like to know if it's possible with foreign keys - it looks it would be a lot less error-prone to me. If it's not possible, is there a technical reason for this?

I'm currently using postgresql, but I'd surely want to know if other systems would be able to implement the above.

Answer 1

-- 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}

Note:

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.

Answer 2

• Why would measurement have a bigint that references a regular int in logger? one or the other (likely just an int)

• If sensor_measurement_data is 1:1 with measurement, just add the data on measurement. Why is this two separate tables?

Suggested schema,

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