在多個其他表中包含一個表的值并允許FK參考

在設計 SQL 資料庫方面，我仍然是一個相對新手，所以如果這是我明顯遺漏的東西，我深表歉意。

對于某些值，我有一些受控詞匯表，我將它們表示為參考受控詞匯表的 FK（我試圖表示的不同詞匯很少）。我的模式規范允許這些詞匯中的每一個也允許一組受控的“未知”資訊值（來自DataCite）。這是一個使用dates必須指定 a的表的示例date_type，它應該是來自date_types或的值unknown_values。我還有幾個使用這個模型的表，每個表都有自己特定的受控詞匯表，但也應該允許來自unknown_values. 因此， 中的值unknown_values應該在許多結構相似的受控詞匯表之間共享date_types。

CREATE TABLE dates ( 
    date_id              integer NOT NULL  PRIMARY KEY autoincrement ,
    date_value           date NOT NULL DEFAULT CURRENT_DATE   ,
    date_type            text NOT NULL    ,
    FOREIGN KEY ( date_type ) REFERENCES date_types( date_type )  
 );

CREATE TABLE date_types ( 
    date_type            text NOT NULL  PRIMARY KEY  ,
    definition           text     
 );

CREATE TABLE unknown_values ( 
    code                 text NOT NULL  PRIMARY KEY  ,
    definition           text     
 );

INSERT INTO date_types (date_type, definition)
VALUES
    ('type_a', 'The first date type'),
    ('type_b', 'The second date type');

INSERT INTO unknown_values (code, definition)
VALUES
    (':unac', 'Temporarily inaccessible'),
    (':unal', 'Unallowed, suppressed intentionally'),
    (':unap', 'Not applicable, makes no sense'),
    (':unas', 'Value unassigned (e.g., Untitled)'),
    (':unav', 'Value unavailable, possibly unknown'),
    (':unkn', 'Known to be unknown (e.g., Anonymous, Inconnue)'),
    (':none', 'Never had a value, never will'),
    (':null', 'Explicitly and meaningfully empty'),
    (':tba', 'To be assigned or announced later'),
    (':etal', 'Too numerous to list (et alia)');

My first thought was a view that creates a union of date_types and unknown_values, but you cannot make FK references onto a view, so that's not suitable.

The "easiest" solution would be to duplicate the values from unknown_values in each controlled vocabulary table (date_types etc.), but this feels incorrect to have duplicated values.

I also thought about a single table for all the controlled vocabularies with a third field (something like vocabulary_category with values like 'date'), so all my tables could reference that one table, but then I would likely need a function and a CHECK constraint to ensure that the value has the right "category". This feels inelegant and messy.

我不知道最好的方法，或者尋找什么來尋求幫助。我無法想象這是一個太罕見的要求，但我似乎無法在網上找到任何解決方案。我的目標資料庫是 SQLite，但我也會對 PostgreSQL 中可能的解決方案感興趣。

uj5u.com熱心網友回復：

您要求的是 FK 具有可選參考表的能力。還發現 Postgres 和 SQLite(?) 都提供了這個選項（afaik 也沒有任何其他 RDBMS）。租賃的 Postgres 提供了一種解決方法，我不知道它在 SQLite 中是否可行。你需要：

• 洗掉當前定義的 FK 上的非空約束
• unknown_values添加參考表的 FK 列
• 添加check要求在列上恰好為 1 date_type且新的 FK 列為空的約束。請參閱num_nulls函式。

您需要的更改：（請參閱演示）

alter table dates 
      alter column date_type 
            drop not null; 
           
alter table dates 
      add  unknown_value text 
           references unknown_values(code); 
          
alter table dates 
      add constraint  one_null 
          check (num_nulls(date_type, unknown_value ) = 1);

注意：Postgres 不支持autoincrement關鍵字。使用生成的列也可以完成相同的操作generated always as identity（對于較舊的 varsions 使用serial）。

標籤：sql sqlite 数据库设计

上一篇：第一個子集之后的SQL子集

下一篇：過濾產品頻率和類別