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