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 thecalendar_id
data was in two places: in the row inentries
and in the row inentry_types
, so I decided to change. Perhaps this was the wrong decision? I needed to keep thecalendar_id
in theentries
table in order to keep theUNIQUE(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?
Best Answer
You only need some small changes. First add a
UNIQUE
constraint oncalendars_entry_types
(this is needed for the new foreign key below):Then remove these two foreign keys from
entries
:and replace them with a composite one: