Enforcing Single NULL in Unique Column Combinations in SQLite

Issue Overview: Unique Constraint with Single NULL Allowed in Multi-Column Index

The core challenge involves enforcing a uniqueness constraint across a combination of columns where one column (z) allows NULL values, but only one row per group of related columns (x, y, epsgid) may have a NULL in z. This mimics PostgreSQL’s NULLS NOT DISTINCT behavior in a database engine (SQLite) that treats all NULLs as distinct in unique indexes. The table schema represents geographic coordinates, where x and y are mandatory, z (height) is optional, and epsgid references a spatial reference system. The goal is to prevent duplicate coordinate entries, treating two coordinates with identical x, y, and epsgid but missing z values as duplicates that should be blocked.

SQLite’s default behavior allows multiple NULLs in a column that is part of a unique index. For example, the unique index (x, y, z, epsgid) would permit unlimited rows where z is NULL, provided x, y, and epsgid are identical. This violates the requirement that only one "NULL height" entry exists per (x, y, epsgid) group. Solutions must address this without relying on PostgreSQL-specific features, arbitrary sentinel values, or overly complex triggers.

Possible Causes: Design Limitations and NULL Handling in SQLite

1. Unique Index NULL Handling: SQLite follows the SQL standard where NULLs are considered distinct in unique constraints. This means that two rows with NULL in a column included in a unique index are not treated as duplicates, even if other columns in the index match. This is the root cause of the problem, as the desired behavior requires treating NULLs as non-distinct for a specific column (z).

2. Schema Design Choices: The use of NULL to represent missing height values introduces ambiguity in uniqueness enforcement. If z were non-nullable with a default "missing" value (e.g., a placeholder like -1.0), standard unique indexes could enforce uniqueness. However, the user rejects this approach due to readability and data consistency concerns.

3. Partial Index Limitations: SQLite supports partial indexes (indexes with a WHERE clause), which can restrict indexed rows to those meeting specific conditions. However, partial indexes alone cannot directly enforce the "single NULL per group" constraint without additional schema modifications or application logic.

4. Trigger Complexity: Triggers can enforce custom constraints by checking for existing NULLs before inserts/updates. However, triggers introduce runtime overhead, require maintenance for schema changes, and may not integrate seamlessly with all ORMs or application frameworks.

5. Normalization Opportunities: Storing 2D (x, y) and 3D (x, y, z) coordinates in separate tables could eliminate the need for NULLs in z. However, this redesign may complicate queries that need to handle both cases and could increase JOIN complexity.

Troubleshooting Steps, Solutions & Fixes

Step 1: Evaluate Schema Normalization

Solution: Split the table into two tables: one for 2D coordinates (without z) and another for 3D coordinates (with z).
Implementation:

CREATE TABLE coord_2d (
  x REAL NOT NULL,
  y REAL NOT NULL,
  epsgid INTEGER NOT NULL REFERENCES epsg(id),
  PRIMARY KEY (x, y, epsgid)
);

CREATE TABLE coord_3d (
  x REAL NOT NULL,
  y REAL NOT NULL,
  z REAL NOT NULL,
  epsgid INTEGER NOT NULL REFERENCES epsg(id),
  PRIMARY KEY (x, y, z, epsgid),
  FOREIGN KEY (x, y, epsgid) REFERENCES coord_2d(x, y, epsgid)
);

Advantages:

• Eliminates NULLs entirely.
• Enforces uniqueness naturally via primary keys.
• Clear separation of 2D and 3D coordinates.

Disadvantages:

• Requires application logic to determine which table to query/update.
• JOINs needed for queries that combine 2D and 3D data.

Testing:
Verify that inserts into coord_2d block duplicates for (x, y, epsgid) and that coord_3d allows multiple z values for the same (x, y, epsgid).

Step 2: Use Generated Columns with Composite Indexes

Solution: Add a generated column indicating whether z is NULL and include it in a unique index.
Implementation:

CREATE TABLE coord (
  id INTEGER PRIMARY KEY NOT NULL,
  x REAL NOT NULL,
  y REAL NOT NULL,
  z REAL NULL DEFAULT NULL,
  z_is_null BOOLEAN GENERATED ALWAYS AS (z IS NULL) VIRTUAL,
  epsgid INTEGER NOT NULL REFERENCES epsg(id)
);

CREATE UNIQUE INDEX idx_coord_unique ON coord (x, y, z, epsgid);
CREATE UNIQUE INDEX idx_coord_null_z ON coord (x, y, epsgid) WHERE z_is_null;

Advantages:

• Allows only one NULL z per (x, y, epsgid) group via idx_coord_null_z.
• Maintains standard uniqueness for non-NULL z via idx_coord_unique.
• Queries remain straightforward if the application includes z_is_null in filters.

Disadvantages:

• Requires SQLite 3.31+ (released Jan 2020) for generated columns.
• Slightly increases storage due to the additional index.

Testing:
Attempt to insert two rows with z = NULL and identical x, y, epsgid. The second insert should fail due to idx_coord_null_z. Non-NULL z values should be enforced by idx_coord_unique.

Step 3: Implement Partial Indexes with Overlapping Conditions

Solution: Create two partial unique indexes: one for non-NULL z and another for NULL z.
Implementation:

CREATE UNIQUE INDEX idx_coord_non_null_z ON coord (x, y, z, epsgid) WHERE z IS NOT NULL;
CREATE UNIQUE INDEX idx_coord_null_z ON coord (x, y, epsgid) WHERE z IS NULL;

Advantages:

• No schema changes required.
• Clear separation of constraints for NULL and non-NULL z.

Disadvantages:

• Queries must explicitly handle z IS NULL or z IS NOT NULL to utilize indexes.
• Application logic must account for both cases when checking for duplicates.

Testing:
Insert rows with z = NULL and identical x, y, epsgid; the second insert should fail. For non-NULL z, duplicates should be blocked by idx_coord_non_null_z.

Step 4: Use Triggers to Enforce Uniqueness

Solution: Create BEFORE INSERT and BEFORE UPDATE triggers to check for existing NULLs.
Implementation:

CREATE TRIGGER trg_prevent_duplicate_null_z
BEFORE INSERT ON coord
FOR EACH ROW
WHEN NEW.z IS NULL
BEGIN
  SELECT RAISE(ABORT, 'Duplicate NULL z for (x, y, epsgid)')
  FROM coord
  WHERE x = NEW.x
    AND y = NEW.y
    AND epsgid = NEW.epsgid
    AND z IS NULL;
END;

CREATE TRIGGER trg_prevent_duplicate_null_z_update
BEFORE UPDATE OF z ON coord
FOR EACH ROW
WHEN NEW.z IS NULL
BEGIN
  SELECT RAISE(ABORT, 'Duplicate NULL z for (x, y, epsgid)')
  FROM coord
  WHERE x = NEW.x
    AND y = NEW.y
    AND epsgid = NEW.epsgid
    AND z IS NULL
    AND id != NEW.id;
END;

Advantages:

• Works on all SQLite versions.
• No schema changes required.

Disadvantages:

• Triggers add overhead for write operations.
• Requires careful handling of updates to z.

Testing:
Attempt to insert or update a row to have z = NULL when another row with the same x, y, epsgid already has z = NULL. The operation should abort with an error.

Step 5: Use COALESCE in Composite Index with Sentinel Value

Solution: Replace NULLs with a sentinel value in the index using COALESCE, paired with a column indicating NULL status.
Implementation:

CREATE UNIQUE INDEX idx_coord_z_sentinel ON coord (
  x,
  y,
  COALESCE(z, -1.7976931348623e+308),
  epsgid,
  (z IS NULL)
);

Advantages:

• Enforces uniqueness by treating all NULLs as equivalent.
• Avoids schema changes.

Disadvantages:

• The sentinel value (-1.7976931348623e+308) must not collide with real data.
• Index cannot be used for direct lookups on z without accounting for COALESCE.

Testing:
Insert two rows with z = NULL and identical x, y, epsgid. The second insert should fail. Non-NULL z values are enforced naturally.

Final Recommendations

1. Use Generated Columns if SQLite 3.31+ is available. This approach balances readability, performance, and maintainability.
2. Opt for Partial Indexes if generated columns are not supported, but be prepared to adjust query logic.
3. Avoid Triggers unless other solutions are infeasible, as they complicate maintenance and degrade write performance.
4. Consider Normalization if the application can tolerate schema changes, as it provides the clearest data model.

Each solution involves trade-offs between schema complexity, query performance, and maintainability. Testing under realistic load conditions is critical to ensure the chosen approach meets performance requirements.