Postgres 有没有办法创建引用另一个表中的日期范围的日期外键？

Question

假设我们有如下两个表，

CREATE TABLE calendar_month (  
  id serial PRIMARY KEY,  
  start_date date NOT NULL,  
  end_date date NOT NULL,  
  reporting_month character varying(50) NOT NULL
);

CREATE TABLE calendar (    
  id serial PRIMARY KEY,  
  holiday bool NOT NULL,  
  actual_date date NOT NULL  
);

在不诉诸触发器的情况下，有没有办法确保在日历表中输入的任何actual_date始终在calendar_month表中有一个相应的reporting_month可以引用？

Answer 1

如果你可以稍微改变你的表，你可以使用外键和检查约束来做到这一点：

CREATE TABLE calendar_month (  
  id serial PRIMARY KEY, 
  start_date date NOT NULL UNIQUE,  
  end_date date NOT NULL,  
  reporting_month character varying(50) NOT NULL
);

CREATE TABLE calendar (    
  id serial PRIMARY KEY,  
  month_start_date date NOT NULL REFERENCES calendar_month(start_date),
  holiday bool NOT NULL,  
  actual_date date NOT NULL CHECK ( 
    actual_date>=month_start_date and
    actual_date<(month_start_date+'1 month'::interval)::date)
);

编辑：Mohan 说过“日历行中的结束日期不一定是=开始日期+ 1 个月间隔”。鉴于我们无法对月份的实际 sart 和结束日期做出任何假设，我们必须稍微做一些事情（正如 Mohan 本人建议的那样）：

CREATE TABLE calendar_month (  
  id serial PRIMARY KEY, 
  start_date date NOT NULL,  
  end_date date NOT NULL,  
  reporting_month character varying(50) NOT NULL,
  UNIQUE(start_date, end_date)
);

CREATE TABLE calendar (    
  id serial PRIMARY KEY,  
  month_start_date date NOT NULL,
  month_end_date date NOT NULL,
  holiday bool NOT NULL,
  foreign key(month_start_date, month_end_date) 
    REFERENCES calendar_month(start_date, end_date) )  
  actual_date date NOT NULL 
    CHECK (actual_date>=month_start_date and actual_date<month_end_date)
);

或者也许 actual_date 应该是 check 约束中的actual_date<=month_end_date - 这取决于您如何定义月份范围。

Answer 2

否：

• 之前更改数据类型的能力
• 外键不提供在比较检查约束仅适用于同一个表，而不适用于任何其他表

。基于所提供的 CREATE TABLE 语句，触发器是您唯一的选择。

Answer 3

如果您的 calendar_month 从该月的第一天开始（这似乎合乎逻辑）：

CREATE TABLE calendar_month (  
  start_date DATE PRIMARY KEY CHECK( start_date = date_trunc( 'month', start_date )),
  reporting_month TEXT NOT NULL
);

INSERT INTO calendar_month( start_date, reporting_month ) VALUES
 ('2010-01-01','january 2010'),
 ('2010-02-01','february 2010');

CREATE TABLE calendar (    
  id serial PRIMARY KEY,  
  month_date DATE NOT NULL REFERENCES calendar_month(start_date),
  actual_date DATE NOT NULL CHECK ( month_date = date_trunc( 'month', actual_date ) )
);

CREATE OR REPLACE FUNCTION calendar_default_trigger_f() RETURNS trigger AS $
BEGIN 
    NEW.month_date = date_trunc( 'month', NEW.actual_date );
    RETURN NEW;
END
$ LANGUAGE plpgsql;

CREATE TRIGGER calendar_default_trigger BEFORE INSERT OR UPDATE ON calendar
  FOR EACH ROW EXECUTE PROCEDURE calendar_default_trigger_f();

INSERT INTO calendar (actual_date) VALUES ('2010-01-12'),('2010-01-31'),('2010-02-28');

test=> SELECT * FROM calendar;
 id | month_date | actual_date 
----+------------+-------------
  2 | 2010-01-01 | 2010-01-12
  3 | 2010-01-01 | 2010-01-31
  4 | 2010-02-01 | 2010-02-28

INSERT INTO calendar (actual_date) VALUES ('2010-04-12');

ERREUR:  une instruction insert ou update sur la table « calendar » viole la contrainte de clé
étrangère « calendar_month_date_fkey »
DÉTAIL : La clé (month_date)=(2010-04-01) n'est pas présente dans la table « calendar_month ».

这使用触发器，但这只是为了方便。实际检查是通过外键完成的。