1

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 & 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?

1

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), 
| improve this answer | |
  • Thank you! I was unaware of composite foreign keys until now. – Scott Colby Jan 2 '19 at 22:40
  • Additional information for future reference: this didn't appear to work at first because SQLite as of version 3.6.19 defaults to not enforcing foreign key constraints. I had to enable them with PRAGMA foreign_keys = ON; to prevent the insertion of "bad" rows. – Scott Colby Jan 2 '19 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.