Constraint via Foreign Key I have the following schema in SQLite:

CREATE TABLE calendars 
  ( 
     id    INTEGER PRIMARY KEY, 
     title TEXT NOT NULL 
  ); 

CREATE TABLE entry_types 
  ( 
     id          INTEGER PRIMARY KEY, 
     red         INTEGER NOT NULL CHECK(red BETWEEN 0 AND 255), 
     green       INTEGER NOT NULL CHECK(green BETWEEN 0 AND 255), 
     blue        INTEGER NOT NULL CHECK(blue BETWEEN 0 AND 255), 
     description TEXT NOT NULL 
  ); 

CREATE TABLE entries 
  ( 
     id            INTEGER PRIMARY KEY, 
     calendar_id   INTEGER NOT NULL, 
     entry_type_id INTEGER NOT NULL, 
     date          TEXT NOT NULL, 
     FOREIGN KEY(calendar_id) REFERENCES calendars(id), 
     FOREIGN KEY(entry_type_id) REFERENCES entry_types(id), 
     UNIQUE(calendar_id, date) 
  ); 

CREATE TABLE calendars_entry_types 
  ( 
     calendar_id   INTEGER NOT NULL, 
     entry_type_id INTEGER UNIQUE NOT NULL, 
     FOREIGN KEY(calendar_id) REFERENCES calendars(id), 
     FOREIGN KEY(entry_type_id) REFERENCES entry_types(id) 
  ); 


Each entry_type can only be used for one particular calendar, which is encoded as the UNIQUE constraint in the calendars_entry_types table, restricting that many-to-many pattern to only one-to-many.


  SIDEBAR: Originally I defined the entry_type table like this:

CREATE TABLE entry_types 
  ( 
     id          INTEGER PRIMARY KEY, 
     -- SNIP color &amp; description columns 
     calendar_id INTEGER NOT NULL, 
     FOREIGN KEY(calendar_id) REFERENCES calendars(id) 
  ); 

  
  This obviated the need for the calendars_entry_types table but then the calendar_id data was in two places: in the row in entries and in the row in entry_types, so I decided to change. Perhaps this was the wrong decision? I needed to keep the calendar_id in the entries table in order to keep the UNIQUE(calendar_id, date) constraint. /SIDEBAR


This is all well and good, but I now have nothing preventing a situation like

INSERT INTO calendars (id, title) VALUES (1, 'test1'), (2, 'test2');
INSERT INTO entry_types (id, red, green, blue, description) VALUES (1, 0, 0, 0, 'entrytype1');

-- entry_type id 1 goes with calendar id 1
INSERT INTO calendars_entry_types (calendar_id, entry_type_id) VALUES (1, 1);

-- this shouldn't work... calendar 2 doesn't go with entry_type 1
INSERT INTO entries (calendar_id, entry_type_id, date) VALUES (2, 1, 'entry1');  -- (I know that's not a date but this is just an example...)


Is it possible to write a constraint that would prevent the insertion of rows to the entries table that match this query?

SELECT id 
FROM   entries 
WHERE  ( calendar_id, entry_type_id ) NOT IN (SELECT calendar_id, 
                                                     entry_type_id 
                                              FROM   calendars_entry_types); 


If not, is there a better or alternative way to design my schema that takes the "each entry type is only valid for a certain calendar" constraint into account?
You only need some small changes. First add a UNIQUE constraint on calendars_entry_types (this is needed for the new foreign key below):

UNIQUE (calendar_id, entry_type_id)


Then remove these two foreign keys from entries:

FOREIGN KEY (calendar_id) REFERENCES calendars (id), 
FOREIGN KEY (entry_type_id) REFERENCES entry_types (id), 


and replace them with a composite one:

FOREIGN KEY (calendar_id, entry_type_id)
    REFERENCES calendars_entry_types (calendar_id, entry_type_id),