通過外鍵約束

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

每個entry_type只能用於一個特定calendar的 ，它被編碼為表中的UNIQUE約束，將calendars_entry_types多對多模式限制為僅一對多。

**邊欄：**最初我是這樣定義entry_type表格的：

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

這消除了對calendars_entry_types錶格的需求，但是calendar_id數據在兩個地方：在行中entries和行中entry_types，所以我決定改變。也許這是錯誤的決定？我需要將 保留calendar_id在entries表中以保留UNIQUE(calendar_id, date)約束。/邊欄

這一切都很好，但我現在沒有什麼能阻止這樣的情況

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

是否可以編寫一個約束來阻止將行插入到entries與該查詢匹配的表中？

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

如果沒有，是否有更好或替代的方法來設計我的架構，將“每個條目類型僅對某個日曆有效”約束考慮在內？

你只需要一些小的改變。首先添加一個UNIQUE約束calendars_entry_types（這是下面的新外鍵所需要的）：

UNIQUE (calendar_id, entry_type_id)

然後從 中刪除這兩個外鍵entries：

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

並用一個複合的替換它們：

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

引用自：https://dba.stackexchange.com/questions/226183