Postgres Is there a way to create a foreign date key that references a date range in another table?

Question

Assuming we have two tables:

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

Without resorting to triggers, is there a way to ensure that any valid_date entered in the calendar table always has the corresponding report_month in the calendar_month table to which it can refer?

Answer 1

if you can change your tables a bit, you can do this with a foreign key and a check constraint:

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

EDIT: , "end_date = start_date + 1- ". , - , - ( ):

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<month_end_date actual_date<=month_end_date check - , .

Answer 2

< p > :

• .
• ,

- , 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 ».

, . .