From c2b9caad691cec7626f1f4d703667889ff4fb5bf Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 18 May 2026 15:53:44 +0000 Subject: [PATCH] Give unique indexes descriptive names The user can see these names on index violations, which are db rule integrity violations. Give them a clue as to what the probem is. --- db/schemas/sokwedb/indexes/create/aggression_event_log.m4 | 5 +++-- db/schemas/sokwedb/indexes/create/aggressions.m4 | 4 +++- db/schemas/sokwedb/indexes/create/arrivals.m4 | 5 ++++- db/schemas/sokwedb/indexes/create/biography_data.m4 | 8 ++++++-- db/schemas/sokwedb/indexes/create/follows.m4 | 4 +++- db/schemas/sokwedb/indexes/create/food_events.m4 | 4 +++- db/schemas/sokwedb/indexes/create/sightings.m4 | 4 +++- db/schemas/sokwedb/indexes/drop/aggression_event_log.m4 | 3 ++- db/schemas/sokwedb/indexes/drop/aggressions.m4 | 3 ++- db/schemas/sokwedb/indexes/drop/arrivals.m4 | 5 ++++- db/schemas/sokwedb/indexes/drop/biography_data.m4 | 7 +++++-- db/schemas/sokwedb/indexes/drop/follows.m4 | 3 ++- db/schemas/sokwedb/indexes/drop/food_events.m4 | 3 ++- db/schemas/sokwedb/indexes/drop/sightings.m4 | 3 ++- 14 files changed, 44 insertions(+), 17 deletions(-) diff --git a/db/schemas/sokwedb/indexes/create/aggression_event_log.m4 b/db/schemas/sokwedb/indexes/create/aggression_event_log.m4 index e1d5d77..9202e9a 100644 --- a/db/schemas/sokwedb/indexes/create/aggression_event_log.m4 +++ b/db/schemas/sokwedb/indexes/create/aggression_event_log.m4 @@ -21,8 +21,9 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS aggression_event_log_year_community - ON aggression_event_log +CREATE UNIQUE INDEX IF NOT EXISTS + "On AGGRESSION_EVENT_LOG, Year + Community must be unique" + ON aggression_event_log (year, community); diff --git a/db/schemas/sokwedb/indexes/create/aggressions.m4 b/db/schemas/sokwedb/indexes/create/aggressions.m4 index 1e02bae..0eeac56 100644 --- a/db/schemas/sokwedb/indexes/create/aggressions.m4 +++ b/db/schemas/sokwedb/indexes/create/aggressions.m4 @@ -21,7 +21,9 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS aggressions_eid ON aggressions +CREATE UNIQUE INDEX IF NOT EXISTS + "AGGRESSIONS has, at most, a 1-to-1 relationship with EVENTS" + ON aggressions (eid); CREATE INDEX IF NOT EXISTS aggressions_community ON aggressions diff --git a/db/schemas/sokwedb/indexes/create/arrivals.m4 b/db/schemas/sokwedb/indexes/create/arrivals.m4 index 3472c1e..2482038 100644 --- a/db/schemas/sokwedb/indexes/create/arrivals.m4 +++ b/db/schemas/sokwedb/indexes/create/arrivals.m4 @@ -21,8 +21,11 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS arrivals_eid ON arrivals +CREATE UNIQUE INDEX IF NOT EXISTS + "ARRIVALS has, at most, a 1-to-1 relationship with EVENTS" + ON arrivals (eid); + CREATE INDEX IF NOT EXISTS arrivals_cycle ON arrivals (cycle); CREATE INDEX IF NOT EXISTS arrivals_datasource ON arrivals diff --git a/db/schemas/sokwedb/indexes/create/biography_data.m4 b/db/schemas/sokwedb/indexes/create/biography_data.m4 index 2ebdc9d..70c0368 100644 --- a/db/schemas/sokwedb/indexes/create/biography_data.m4 +++ b/db/schemas/sokwedb/indexes/create/biography_data.m4 @@ -22,9 +22,13 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS biography_data_animidnum ON biography_data +CREATE UNIQUE INDEX IF NOT EXISTS + "BIOGRAPHY_DATA.AnimIDNum must be unique" + ON biography_data (animidnum); -CREATE UNIQUE INDEX IF NOT EXISTS biography_data_animname ON biography_data +CREATE UNIQUE INDEX IF NOT EXISTS + "BIOGRAPHY_DATA.AnimName must be unique" + ON biography_data (animname); CREATE INDEX IF NOT EXISTS biography_data_momid ON biography_data diff --git a/db/schemas/sokwedb/indexes/create/follows.m4 b/db/schemas/sokwedb/indexes/create/follows.m4 index 3e06632..6b13771 100644 --- a/db/schemas/sokwedb/indexes/create/follows.m4 +++ b/db/schemas/sokwedb/indexes/create/follows.m4 @@ -21,7 +21,9 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS follows_date_focal ON follows +CREATE UNIQUE INDEX IF NOT EXISTS + "On FOLLOWS, Date + Focal must be unique" + ON follows (date, focal); CREATE INDEX IF NOT EXISTS follows_focal ON follows diff --git a/db/schemas/sokwedb/indexes/create/food_events.m4 b/db/schemas/sokwedb/indexes/create/food_events.m4 index 57bb7a2..b304dc7 100644 --- a/db/schemas/sokwedb/indexes/create/food_events.m4 +++ b/db/schemas/sokwedb/indexes/create/food_events.m4 @@ -21,7 +21,9 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS food_events_eid_seq ON food_events +CREATE UNIQUE INDEX IF NOT EXISTS + "On FOOD_EVENTS, EID + Seq must be unique" + ON food_events (eid, seq); CREATE INDEX IF NOT EXISTS food_events_foodpart ON food_events diff --git a/db/schemas/sokwedb/indexes/create/sightings.m4 b/db/schemas/sokwedb/indexes/create/sightings.m4 index 67b7451..6920615 100644 --- a/db/schemas/sokwedb/indexes/create/sightings.m4 +++ b/db/schemas/sokwedb/indexes/create/sightings.m4 @@ -21,7 +21,9 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -CREATE UNIQUE INDEX IF NOT EXISTS sightings_date_animid ON sightings +CREATE UNIQUE INDEX IF NOT EXISTS + "On SIGHTINGS, Date + AnimID must be unique" + ON sightings (date, animid); CREATE INDEX IF NOT EXISTS sightings_animid ON sightings diff --git a/db/schemas/sokwedb/indexes/drop/aggression_event_log.m4 b/db/schemas/sokwedb/indexes/drop/aggression_event_log.m4 index b2dacf9..ebbf28f 100644 --- a/db/schemas/sokwedb/indexes/drop/aggression_event_log.m4 +++ b/db/schemas/sokwedb/indexes/drop/aggression_event_log.m4 @@ -21,4 +21,5 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS aggression_event_log_year_community; +DROP INDEX IF EXISTS + "On AGGRESSION_EVENT_LOG, Year + Community must be unique"; diff --git a/db/schemas/sokwedb/indexes/drop/aggressions.m4 b/db/schemas/sokwedb/indexes/drop/aggressions.m4 index 493b7d2..d1677d8 100644 --- a/db/schemas/sokwedb/indexes/drop/aggressions.m4 +++ b/db/schemas/sokwedb/indexes/drop/aggressions.m4 @@ -21,6 +21,7 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS aggressions_eid; +DROP INDEX IF EXISTS + "AGGRESSIONS has, at most, a 1-to-1 relationship with EVENTS"; DROP INDEX IF EXISTS aggressions_community; diff --git a/db/schemas/sokwedb/indexes/drop/arrivals.m4 b/db/schemas/sokwedb/indexes/drop/arrivals.m4 index b053c24..a9cd325 100644 --- a/db/schemas/sokwedb/indexes/drop/arrivals.m4 +++ b/db/schemas/sokwedb/indexes/drop/arrivals.m4 @@ -21,6 +21,9 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS arrivals_eid; +DROP INDEX IF EXISTS + "ARRIVALS has, at most, a 1-to-1 relationship with EVENTS"; + + DROP INDEX IF EXISTS arrivals_cycle; DROP INDEX IF EXISTS arrivals_datasource; diff --git a/db/schemas/sokwedb/indexes/drop/biography_data.m4 b/db/schemas/sokwedb/indexes/drop/biography_data.m4 index e4c7a8f..5db1050 100644 --- a/db/schemas/sokwedb/indexes/drop/biography_data.m4 +++ b/db/schemas/sokwedb/indexes/drop/biography_data.m4 @@ -22,8 +22,11 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS biography_data_animidnum; -DROP INDEX IF EXISTS biography_data_animname; +DROP INDEX IF EXISTS + "BIOGRAPHY_DATA.AnimIDNum must be unique"; +DROP INDEX IF EXISTS + "BIOGRAPHY_DATA.AnimName must be unique"; + DROP INDEX IF EXISTS biography_data_momid; DROP INDEX IF EXISTS biography_data_dadid; DROP INDEX IF EXISTS biography_data_birthdate; diff --git a/db/schemas/sokwedb/indexes/drop/follows.m4 b/db/schemas/sokwedb/indexes/drop/follows.m4 index 658976b..d2569cf 100644 --- a/db/schemas/sokwedb/indexes/drop/follows.m4 +++ b/db/schemas/sokwedb/indexes/drop/follows.m4 @@ -22,7 +22,8 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS follows_date_focal; +DROP INDEX IF EXISTS + "On FOLLOWS, Date + Focal must be unique"; DROP INDEX IF EXISTS follows_focal; DROP INDEX IF EXISTS follows_community; diff --git a/db/schemas/sokwedb/indexes/drop/food_events.m4 b/db/schemas/sokwedb/indexes/drop/food_events.m4 index dccf236..2b12287 100644 --- a/db/schemas/sokwedb/indexes/drop/food_events.m4 +++ b/db/schemas/sokwedb/indexes/drop/food_events.m4 @@ -21,7 +21,8 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS food_events_eid_seq; +DROP INDEX IF EXISTS + "On FOOD_EVENTS, EID + Seq must be unique"; DROP INDEX IF EXISTS food_events_foodpart; DROP INDEX IF EXISTS food_events_foodname; diff --git a/db/schemas/sokwedb/indexes/drop/sightings.m4 b/db/schemas/sokwedb/indexes/drop/sightings.m4 index 815578c..802a927 100644 --- a/db/schemas/sokwedb/indexes/drop/sightings.m4 +++ b/db/schemas/sokwedb/indexes/drop/sightings.m4 @@ -21,7 +21,8 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl -DROP INDEX IF EXISTS sightings_date_animid; +DROP INDEX IF EXISTS + "On SIGHTINGS, Date + AnimID must be unique"; DROP INDEX IF EXISTS sightings_animid; DROP INDEX IF EXISTS sightings_commid; -- 2.34.1