From cefab7cda25a8f5ba4769087affd4906c8060e08 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 18 May 2026 15:27:52 +0000 Subject: [PATCH] An individual can only appear in an event once --- db/schemas/sokwedb/indexes/create/roles.m4 | 10 ++++++++-- db/schemas/sokwedb/indexes/drop/roles.m4 | 3 ++- doc/src/tables/roles.m4 | 4 ++++ 3 files changed, 14 insertions(+), 3 deletions(-) diff --git a/db/schemas/sokwedb/indexes/create/roles.m4 b/db/schemas/sokwedb/indexes/create/roles.m4 index a0335e8..b299c3f 100644 --- a/db/schemas/sokwedb/indexes/create/roles.m4 +++ b/db/schemas/sokwedb/indexes/create/roles.m4 @@ -21,10 +21,16 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +-- Index for purposes of data integrity. +-- Don't index Participant separately. It is almost always going to +-- be search for in conjunction with eid anyway. +CREATE UNIQUE INDEX IF NOT EXISTS + "On ROLES, Participant + EID must be unique" + ON roles + (participant, eid); + CREATE INDEX IF NOT EXISTS roles_eid ON roles (eid); CREATE INDEX IF NOT EXISTS roles_role ON roles (role); -CREATE INDEX IF NOT EXISTS roles_participant ON roles - (participant); diff --git a/db/schemas/sokwedb/indexes/drop/roles.m4 b/db/schemas/sokwedb/indexes/drop/roles.m4 index 5429d10..61b97a1 100644 --- a/db/schemas/sokwedb/indexes/drop/roles.m4 +++ b/db/schemas/sokwedb/indexes/drop/roles.m4 @@ -21,8 +21,9 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +DROP INDEX IF EXISTS + "On ROLES, Participant + EID must be unique"; DROP INDEX IF EXISTS roles_eid; DROP INDEX IF EXISTS roles_role; -DROP INDEX IF EXISTS roles_participant; diff --git a/doc/src/tables/roles.m4 b/doc/src/tables/roles.m4 index e7b8d75..5485cb8 100644 --- a/doc/src/tables/roles.m4 +++ b/doc/src/tables/roles.m4 @@ -51,6 +51,10 @@ same as or before the starting time\ [#f1]_ of the given event and an |EVENTS|.\ |EVENTS.Stop| that is the same as or after the given event's ending time\ [#f2]_. +An individual can only be a participant once, in any given event. +This means that the combination of |ROLES.Participant| and |ROLES.EID| +must be unique. + For further information on the required, and expected, relationship between ROLES, |EVENTS|, and other tables see the documentation of the |EVENTS| table. -- 2.34.1