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

Assuming we have two tables as follows

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开发者_JAVA技巧 (    
  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 actual_date entered in the calendar table always has a corresponding reporting_month in the calendar_month table that it can refer to?

---

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: Mohan has said that "the end_date in the calendar row will not necessarily be = start_date+ 1 month interval". Give that we can't make any assumptions about the actual sart and end dates of the months, we have to do something slightly (as Mohan himself suggests):

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

Or perhaps actual_date<month_end_date should be actual_date<=month_end_date in the check constraint - it depends how you define your month bounds.

---

No:

• Foreign keys don't provide the ability to change the data type before comparison
• check constraints only apply to the same table, not any others

A trigger is your only option based on the CREATE TABLE statements provided.

---

If your calendar_month's start at the first day of the month (which would seem logical) :

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

This uses a trigger, but it is only for convenience. Actual checking is done by foreign keys.