Solution for what you ask
Assuming you want to enforce that:
"Id_Lot" actually exists in "Lot"."Code". -> FK constraint"Lot"."Empty" for the spot is TRUE at the time of the check only.
You could do this with a NOT VALID CHECK constraint using a fake IMMUTABLE function to check on the other table. Details:
But your data model is shaky in a number of aspects. I would suggest a much cleaner approach.
Cleaner design with exclusion constraint
Don't store whether a lot is currently empty redundantly with the lot. That's very error prone and susceptible to concurrency issues. Enforce that each lot can only be taken once at a time with an exclusion constraint. For that to work, save the time of exit in ticket, additionally.
CREATE TABLE lot ( lot_id varchar(4) NOT NULL PRIMARY KEY -- I would use integer if possible , lot_type text NOT NULL );
No redundant current state in the lot table.
For the exclusion constraint to work, you need the additional module btree_gist. Detailed instructions:
CREATE TABLE ticket ( ticket_id serial PRIMARY KEY , during tsrange NOT NULL , license_plate text NOT NULL REFERENCES "Vehicle"("L_Plate"), , lot_id int NOT NULL REFERENCES lot , CONSTRAINT lot_uni_ticket EXCLUDE USING gist (lot_id WITH =, during WITH &&) , CONSTRAINT during_lower_bound_not_null CHECK (NOT lower_inf(during)) , CONSTRAINT during_bounds CHECK (lower_inc(during) AND NOT upper_inc(during)) );
Using a timestamp range data type tsrange for the parking duration during. Enter with upper bound NULL, when the car enters. Update with upper bound when the car exits. Among other things, this also makes it possible for cars to park multiple days.
Some additional CHECK constraints to enforce basic rules on during:
- Inclusive lower bound, exclusive upper bound to stay consistent.
- The lower bound (entrance) can never be missing.
Related:
"Id_Lot"is "empty" at the time of the check (once), or verify it is "empty" permanently? It really depends on the specifics of your model.