Лучший дизайн схемы для связи между таблицами, которая осуществляет целостность

Я думаю, что Вам нужны утверждения стандарта SQL, которые (к сожалению), в основном не реализованы фактическим DBMS.

Все ответы соглашаются, что существует три первичных таблицы под названием TableA, TableB и TableC, каждый содержащий его собственный столбец ID:

TableA (A_ID PRIMARY KEY, ...)
TableB (B_ID PRIMARY KEY, ...)
TableC (C_ID PRIMARY KEY, ...)

Не ясно из описания проблемы, может ли единственное значение B иметь несколько родительские записи. Ясно, что единственный C может иметь несколько родительских записей B. Если B связывается с единственным A, дизайн TableB может быть пересмотрен к:

TableB (B_ID, ..., A_ID REFERENCES TableA)

Если B может быть связан с несколькими различными A, то соединение лучше всего представлено присоединяющейся таблицей:

A_and_B (A_ID REFERENCES TableA,
         B_ID REFERENCES TableB,
         PRIMARY KEY (A_ID, B_ID)
        )

Также не ясно из описания, должен ли C, связанный с B, быть тем же для каждого, что B связан с, или может ли другой A сослаться на тот же B, и набор C, связанного с B для A1, может отличаться от набора C, связанного с B для A2. (Конечно, если единственный B может только быть связан с одним A, эта проблема спорна.)

В целях этого ответа я собираюсь предположить, что любой B связан с единственным A, таким образом, структура TableB включает A_ID как внешний ключ. Так как единственный C может быть связан с несколькими B, соответствующая структура является новой таблицей присоединения:

B_and_C (B_ID REFERENCES TableB,
         C_ID REFERENCES TableC,
         PRIMARY KEY (B_ID, C_ID)
        )

При упрощении (путем исключения правил о deferrability и непосредственности) утверждение похоже:

CREATE ASSERTION assertion_name CHECK ( <search_condition> )

Так, после того как у нас есть ряд проектных решений, мы можем записать утверждение для проверки данных. Учитывая таблицы TableA, TableB (с внешним ключом A_ID), TableC и B_and_C, требование - то, что количество случаев данного C_ID через полный A равняется 1.

CREATE ASSERTION only_one_instance_of_c_per_a CHECK
(
     NOT EXISTS (
         SELECT A_ID, COUNT(C_ID)
             FROM TableB JOIN B_and_C USING (C_ID)
             GROUP BY A_ID
             HAVING COUNT(C_ID) > 1
     )
)

[Исправленный: Я думаю, что это более точно:

CREATE ASSERTION only_one_instance_of_c_per_a CHECK
(
     NOT EXISTS (
         SELECT A_ID, C_ID, COUNT(*)
             FROM TableB JOIN B_and_C USING (C_ID)
             GROUP BY A_ID, C_ID
             HAVING COUNT(*) > 1
     )
)

]

Набор условий объединения меняется в зависимости от других правил для того, как таблицы соединены, но полная ограничительная структура остается, то же - там не должно существовать больше чем одна ссылка на данный C_ID для конкретного A_ID.

В комментариях ниже, meandmycode примечания:

Я получаю чувство, что существует дефект в моем дизайне. Моя логика реального мира - то, что 'B' всегда имеет по крайней мере один дочерний 'C'. Это не имеет смысла, учитывая, что 'B' должен существовать, прежде чем его ребенок может быть присоединен. База данных в настоящее время позволяла бы 'B' быть присоединенным, не имея ПО КРАЙНЕЙ МЕРЕ ОДНОГО 'C'.. ребенок, я - попытка как таковая пересмотреть 'B' так, чтобы это имело поле, которое относится к его основному дочернему 'C', а также наличию дочернего набора дополнительного 'C, но теперь у меня есть набор, который мог также включать основной 'C', указанный 'B', который будет.. неправильно.

Существует ли шаблон дб, который вывел бы 'одни или несколько дети' правило, по сравнению с нулем или больше?

Я думаю, что у Вас действительно есть проблемы с Вашей моделью. Трудно создать B, если там должен уже существовать C, который относится к недавно созданному B, особенно если C должен только обратиться к существующему B. Фраза 'курица и яйцо' приходит на ум. Так, обычно, Вы позволяете B иметь нуль или больше C в контексте как это.

Вы все еще не предусмотрели, имеет ли TableB внешний ключ A_ID или есть ли у Вас связывающая таблица как A_and_B. Если это имеет внешний ключ, то, по-видимому, Вы не можете создать B, пока Вы не создали, к которому это относится.

Я не думаю включая один идентификатор C в таблице B, хорошая идея - это делает для асимметричной обработки (более твердый SQL). Это также означает, что, если необходимо удалить тот один C, необходимо обновить вещи так, чтобы одна из других ссылок C была удалена из таблицы, в которой это в настоящее время находится, и затем обновите значение в записи B. Это грязно, чтобы быть вежливым об этом.

Я думаю, что необходимо исправить вопрос определить фактическую структуру таблицы, Вы смотрите на - вдоль строк, показанных в различных ответах; можно использовать тройные точки для представления других но несоответствующих столбцов. Утверждение, которое я предложил, должно будет, вероятно, быть реализовано как своего рода триггер - который входит в определенные для DBMS нотации.

Из исправленного описания кратких сводок (A), представления (B) и участники (C), ясно, что единственное представление относится всего к одному резюме, так, чтобы представления могли иметь простой внешний ключ, который определяет резюме, для которого это является представление. И участник может только сотрудничать на одном представлении для конкретного резюме. Будет таблица 'submission_collaborators' со столбцами для идентификации представления и участника, комбинация является первичным ключом, и каждый столбец является внешним ключом.

Briefs(Brief_ID, ...)
Submissions(Submission_ID, Brief_ID REFERENCES Briefs, ...)
Members(Member_ID, ...)
Submission_Collaborators(Submission_ID REFERENCES Submissions,
                         Member_ID REFERENCES Members,
                         PRIMARY KEY (Submission_ID, Member_ID)
                        )

Следовательно, требование - то, что следующий запрос не должен возвращать строки:

SELECT s.brief_id, c.member_id, COUNT(*)
    FROM submissions AS s JOIN submission_collaborators AS c
         ON s.submission_id = c.submission_id
    GROUP BY s.brief_id, c.member_id
    HAVING COUNT(*) > 1

Это - тот же запрос, который я встроил в СОЗДАТЬ УТВЕРЖДЕНИЕ (второй вариант). Можно откопать дополнительную информацию (краткий заголовок, заголовок представления, имя элемента, различные даты, и т.д.) также, но ядро проблемы - то, что показанный запрос не должен возвращать данные.