如何定义此约束？ [英] How I can define this constraint?

问题描述

我正在为Postgres中的停车场设计一个数据库。我需要定义一个约束（或类似约束）以验证票证中的 Id_Lot 在表 Lot 中是否为空

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

我该怎么做？

相关表格如下：

门票：

"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

推荐答案

解决方案

假设您要强制执行以下操作：

Solution for what you ask

Assuming you want to enforce that:

1. Id_Lot 实际上存在于全部。代码 中。 -> FK约束


2. 全部。空 的位置为 TRUE 仅在检查时。

1. "Id_Lot" actually exists in "Lot"."Code". -> FK constraint
2. "Lot"."Empty" for the spot is TRUE at the time of the check only.

您可以使用 无效 CHECK 约束，使用伪造的 IMMUTABLE 函数检查另一个表。详细信息：

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.

不要存储是否很多目前有很多空缺。这很容易出错，容易出现并发问题。强制每个批次只能一次使用 排除约束 。为此，请另外在 ticket 中保存退出时间。

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.

要使排除约束生效，您需要附加模块 btree_gist 。详细说明：

For the exclusion constraint to work, you need the additional module btree_gist. Detailed instructions:

• 时间范围的一对多关系完整性问题


• 存储星期几和时间？

• 'one-to-many' relation integrity issue for time ranges
• Store the day of the week and time?

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

• 使用时间戳范围数据类型> tsrange 停车期间 。
  当汽车驶入时以上限NULL输入。汽车驶出时，以上限更新。
  除其他外，这还使汽车可以停放多天。

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

    一些其他 CHECK 约束以在期间执行基本规则：

    
    
    • 包括下限，独占上限保持一致。
    
    
    • 下界（入口）永远不会丢失。
      
      
      
      
      • PostgreSQL tsrange：lower_inf（'（-infinity，today）':: tsrange）为假是否正确？
      
      
      • 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?

        相关：

        
        
        • 如何从Postgres的预订中查找第一个免费的开始时间
        
        
        • How to find first free start times from reservations in Postgres

        这篇关于如何定义此约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！