sql - How to create relationship in DB

Question

I have to create three tables ACCOUNTING_POINT as AP, METER_CHANGE as MC, and METER as M the table MC will conjunct AP with M but I should be sure that M.id would be unique among rows that refer from AP to MC. So I should be sure that AP would never refer on some MC with the same M.id. It looks like this:

Here a picture which describes my problem the red row indicate the problem

I know that I can use attribute "actual" in the table MC and create pk_meter_id_actual = unique but I would like to avoid using this attribute. I should keep MC.ap_id for history and AP.meter_change for connection

question from:https://stackoverflow.com/questions/66063761/how-to-create-relationship-in-db

Answer 1

It looks like this:

I have solved it by adding boolean attribute "actual" into tthe table MC and two constraints: CREATE UNIQUE INDEX meter_id ON MC (meter_id, actual) WHERE (actual is true); CREATE UNIQUE INDEX ap_id ON MC (ap_id, actual) WHERE (actual is true);