tl;dr как лучше делать подчинение одной таблицы многим таблицам (но для одной записи — один владелец). Много опциональных FK или один INTEGER с ручной проверкой?
Вот к примеру, есть у меня таблицы с разными «сущностями», и надо ко всем дать возможность «прикреплять документы». Делаю так:
CREATE TABLE objects (object INTEGER PRIMARY KEY);
CREATE TABLE tasks (task INTEGER PRIMARY KEY);
CREATE TABLE records (record INTEGER PRIMARY KEY);
CREATE TABLE documents (
object INTEGER, -- возможный владелец
task INTEGER, -- возможный владелец
record INTEGER, -- возможный владелец, но...
side INTEGER NOT NULL DEFAULT 0, -- ... у него несколько "сторон"
-- тут собственно описание документа
name TEXT NOT NULL, -- неуникальное
originalName TEXT NOT NULL, -- неуникальное
relativePath TEXT NOT NULL, -- неуникальное
position INTEGER NOT NULL, -- (!) уникальное, для сортировки
-- гарантируем уникальность по владельцу и позиции
UNIQUE (object, task, record, side, position),
FOREIGN KEY (object)
REFERENCES objects (object)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (task)
REFERENCES tasks (task)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (record)
REFERENCES records (record)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Сразу скажу, что все это работает через прослойку, т.е. работа с данными происходит без знания о том, что все это бачится на SQL-е. Сделано для того, чтобы можно было прозрачно менять бакенд. Кстати у базы только один эксклюзивный коннект.
У всех трех владельцев сквозная нумерация, так что PRIMARY KEY уникален внутри всех трех таблиц. Выборка документов планируется только внутри владельца, то есть выяснять к какой таблице относится ключ владельца — ненужно. Т.о. можно было бы сделать только одно поле владельца «owner INTEGER NOT NULL» без FOREIGN KEY (оставив костыль side для record) и самому реализовать каскады. Какой подход более правильный?
Теперь второй вопрос, чем-то схожий. У objects есть значения атрибутов в отдельной таблице (схема типизации в таблицах objectTypes и objectAttributes, здесь не привожу):
CREATE TABLE objectValues (
object INTEGER NOT NULL, -- FK
attribute INTEGER NOT NULL, -- FK
objectValue INTEGER, -- или это
scalarValue BLOB, -- или это
PRIMARY KEY (object, attribute),
FOREIGN KEY (object)
REFERENCES objects (object)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (attribute)
REFERENCES objectAttributes (attribute)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (objectValue)
REFERENCES objects (object)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
И побочный вопрос: если запись однозначно идентифицируется по полю TEXT, нужно ли создавать синтетический первичный ключ, или можно спокойно использовать TEXT как FK?
