How I can define this constraint?

Question

I am designing a database for a parking lot in Postgres. I need to define a constraint (or similar) to verify the Id_Lot in Ticket is empty in the table "Lot".

How I can do it?

The relevant tables are:

Ticket:

"Id_Ticket"         serial PRIMARY KEY,
"Date_Entrance"     date NOT NULL,
"Time_Entrance"     time without time zone NOT NULL,
"License_plate"     varchar(6) NOT NULL references "Vehicle"("L_Plate"), 
"Id_Lot"            varchar(4) NOT NULL references "Lot"("Code")

Lot:

"Code"              varchar(4) NOT NULL PRIMARY KEY,
"Type"              varchar(5) NOT NULL,
"Empty"             boolean NOT NULL

Probably with a constraint trigger. http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html — Denis de Bernardy, Dec 06 '14 at 23:17
I think that I wouldn't know when a customer is going to return... So I will save this information in a table "Bill" that's the final payment. — jonango, Dec 06 '14 at 23:34
Verify `"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. — Erwin Brandstetter, Dec 07 '14 at 22:19
Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names — , Mar 23 '20 at 06:27

Erwin Brandstetter · Accepted Answer · 2020-03-23T04:23:19.177

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:

Disable all constraints and table checks while restoring a dump

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.
  - PostgreSQL tsrange: is it correct for lower_inf('(-infinity,today)'::tsrange) to be false?

How to find first free start times from reservations in Postgres

How I can define this constraint?

1 Answers1

Solution for what you ask

Cleaner design with exclusion constraint

Linked