Issue Overview: Cohort-Moiety Uniqueness Constraint Violations

The core challenge revolves around enforcing a business rule where the cohort column must be uniquely associated with a single moiety value across all rows in the catalog table. Once a cohort is assigned to a moiety, subsequent inserts or updates attempting to associate that cohort with a different moiety must be rejected. This is a functional dependency where cohort determines moiety, but the current schema does not enforce this relationship.

For example:

• Inserting ('9abc','001','2','ken') after ('5678','000','2','sue') should fail because cohort 2 is already linked to moiety 000.
• Inserting ('def0','000','2','ann') after ('5678','000','2','sue') is permitted because both rows share the same cohort-moiety pair (2–000).

The existing UNIQUE constraints (hash uniqueness and recid primary key) do not address this requirement. A naive UNIQUE(moiety, cohort) constraint would incorrectly allow multiple rows with the same cohort if their moiety values differ (e.g., cohort 2 with moiety 000 and 001), violating the business rule. Conversely, a UNIQUE(cohort) constraint would overly restrict the table by allowing only one row per cohort, which is not desired since multiple rows can share the same cohort-moiety pair.

Possible Causes: Schema Design Limitations and Functional Dependency Violations

1. Lack of Normalization:
The catalog table combines two distinct entities:

• Cohort-Moiety Mappings (functional dependency: cohort → moiety)
• Entity-Specific Attributes (hash, name, recid)

Storing these together violates Third Normal Form (3NF) because moiety is transitively dependent on the primary key (recid) via cohort. This creates redundancy and update anomalies. For instance, changing the moiety for a cohort would require updating all rows with that cohort, risking inconsistencies.

2. Absence of Trigger-Based Validation:
SQLite does not natively support assertions or check constraints that reference other rows. Without triggers, the database cannot enforce cross-row validation rules like "no two rows may have the same cohort with different moieties."

3. Misunderstanding Unique Constraints:
A UNIQUE(cohort, moiety) constraint ensures that each cohort-moiety pair appears only once, but it does not prevent a cohort from appearing with multiple moieties. For example, it would allow:

INSERT INTO catalog VALUES(NULL,'1234','000','1','bob');  -- Allowed
INSERT INTO catalog VALUES(NULL,'5678','001','1','sue');  -- Also allowed (same cohort, different moiety)

This violates the requirement that a cohort belongs to exactly one moiety.

Troubleshooting Steps, Solutions & Fixes

Step 1: Normalize the Schema

Split the catalog table into two tables to separate cohort-moiety mappings from entity-specific data:

Table 1: cohort_moiety

CREATE TABLE cohort_moiety (
  cohort TEXT PRIMARY KEY,
  moiety TEXT NOT NULL
);

Table 2: entity

CREATE TABLE entity (
  recid INTEGER PRIMARY KEY,
  hash TEXT UNIQUE NOT NULL,
  cohort TEXT NOT NULL REFERENCES cohort_moiety(cohort),
  name TEXT NOT NULL
);

How This Works:

• The cohort_moiety table enforces a one-to-one relationship between cohort and moiety via its primary key on cohort.
• The entity table references cohort_moiety via a foreign key, ensuring that every cohort in entity corresponds to exactly one moiety.

Example Workflow:

1. Insert cohort-moiety mappings first:

   INSERT INTO cohort_moiety VALUES ('1', '000'), ('2', '000'), ('3', '001'), ('4', '001');
   

2. Insert entities:

   INSERT INTO entity VALUES (NULL, '1234', '1', 'bob');  -- Allowed
   INSERT INTO entity VALUES (NULL, '5678', '2', 'sue');  -- Allowed
   INSERT INTO entity VALUES (NULL, '9abc', '2', 'ken');  -- Allowed (same cohort-moiety)
   INSERT INTO entity VALUES (NULL, 'def0', '2', 'ann');  -- Allowed
   

3. Attempting to insert a new cohort-moiety pair for an existing cohort fails:

   INSERT INTO cohort_moiety VALUES ('2', '001');  -- Fails (primary key violation)
   

Advantages:

• Eliminates redundancy: Moiety is stored once per cohort.
• Simplifies updates: Changing a cohort’s moiety requires a single update in cohort_moiety.
• Enforces the business rule at the schema level.

Step 2: Implement Triggers for Non-Normalized Schemas

If schema changes are infeasible, use triggers to enforce the cohort-moiety dependency in the original catalog table.

Create a Trigger for Inserts:

CREATE TRIGGER enforce_cohort_moiety_insert
BEFORE INSERT ON catalog
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'Cohort already associated with a different moiety')
  FROM catalog
  WHERE cohort = NEW.cohort AND moiety <> NEW.moiety
  LIMIT 1;
END;

Create a Trigger for Updates:

CREATE TRIGGER enforce_cohort_moiety_update
BEFORE UPDATE ON catalog
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'Cohort already associated with a different moiety')
  FROM catalog
  WHERE cohort = NEW.cohort AND moiety <> NEW.moiety AND recid <> NEW.recid
  LIMIT 1;
END;

How This Works:

• The insert trigger checks if any existing row has the same cohort but a different moiety. If found, the insert is aborted.
• The update trigger performs the same check but skips the current row (to allow updating other columns without triggering a false positive).

Testing the Triggers:

INSERT INTO catalog VALUES(NULL,'1234','000','1','bob');  -- Allowed
INSERT INTO catalog VALUES(NULL,'5678','000','2','sue');  -- Allowed
INSERT INTO catalog VALUES(NULL,'9abc','001','2','ken');  -- Fails (trigger)
INSERT INTO catalog VALUES(NULL,'def0','000','2','ann');  -- Allowed

Limitations:

• Triggers add overhead to write operations.
• Requires careful handling of concurrent transactions.

Step 3: Hybrid Approach with Partial Indexes (Advanced)

For databases where neither normalization nor triggers are viable, use a partial index to enforce the cohort-moiety dependency indirectly. This method is less intuitive but avoids triggers.

Create a Unique Index on cohort:

CREATE UNIQUE INDEX idx_cohort_moiety ON catalog(cohort);

How This Works:

• The unique index on cohort prevents multiple rows from having the same cohort value.
• Disadvantage: This overly restricts the table by allowing only one row per cohort, which is not desired if multiple entities can share the same cohort-moiety pair.

Workaround with NULLs (If Applicable):
If some cohorts are allowed to have multiple moieties (not the case here), use a filtered index:

CREATE UNIQUE INDEX idx_cohort_moiety_not_null ON catalog(cohort) WHERE moiety IS NOT NULL;

Applicability:
This approach is only viable if the business rule allows at most one moiety per cohort, which aligns with the original requirement but prohibits multiple entities sharing the same cohort-moiety pair.

Final Recommendation:
Normalize the schema by splitting the table into cohort_moiety and entity. This approach is the most robust, scalable, and maintainable. Use triggers only if schema changes are impossible. Avoid partial indexes unless the business rules align perfectly with their limitations.