Preventing Orphaned Rows in SQLite with Referential Integrity and Triggers

Understanding the Problem: Orphaned Rows in Entity-Component Systems

In database design, particularly within an entity-component system, orphaned rows can become a significant issue. An entity-component system is a design pattern often used in game development and other domains where entities (e.g., game objects) are composed of various components (e.g., position, health, etc.). In such systems, components are often de-duplicated to save space and improve performance. However, this de-duplication can lead to a situation where a component exists in the database but is no longer referenced by any entity. These unreferenced components are known as orphaned rows.

The core issue here is ensuring that every component row in the database is referenced by at least one entity. If a component is not referenced by any entity, it should be deleted to maintain data integrity and optimize storage. This is not just a matter of database normalization but also a business rule that ensures the database remains clean and efficient.

Why Traditional Referential Integrity Falls Short

Traditional referential integrity constraints in SQLite, such as foreign keys, are designed to ensure that a row in a child table references a valid row in a parent table. However, they do not enforce the reverse condition: that a row in the parent table must be referenced by at least one row in the child table. This limitation makes it impossible to use standard foreign key constraints to prevent orphaned rows.

For example, consider a chroma_keys table that stores color parameters used by various media entities. If a chroma_key is no longer referenced by any media entity, it should be deleted. However, SQLite’s foreign key constraints cannot enforce this rule directly. This is where triggers and custom logic come into play.

Implementing Triggers to Enforce Referential Integrity

Triggers in SQLite are powerful tools that allow you to automatically execute SQL statements in response to specific events, such as inserts, updates, or deletes on a table. By using triggers, you can enforce complex business rules that go beyond the capabilities of standard foreign key constraints.

Creating After-Delete Triggers

One approach to preventing orphaned rows is to create after-delete triggers on the child tables that reference the parent table. These triggers would check if the deleted row was the last reference to a row in the parent table and, if so, delete the corresponding row in the parent table.

For example, suppose you have a media table and a media_streams table, both of which reference the chroma_keys table. You could create after-delete triggers on both the media and media_streams tables to ensure that any chroma_key no longer referenced by either table is deleted.

CREATE TRIGGER media_after_delete_1 AFTER DELETE ON media
BEGIN
  DELETE FROM chroma_keys
   WHERE id = OLD.chroma_key_id
    AND NOT EXISTS (
            SELECT *
             FROM media
            WHERE chroma_key_id = OLD.chroma_key_id
           )
    AND NOT EXISTS (
            SELECT *
             FROM media_streams
            WHERE chroma_key_id = OLD.chroma_key_id
           );
END;

CREATE TRIGGER media_streams_after_delete_1 AFTER DELETE ON media_streams
BEGIN
  DELETE FROM chroma_keys
   WHERE id = OLD.chroma_key_id
    AND NOT EXISTS (
            SELECT *
             FROM media
            WHERE chroma_key_id = OLD.chroma_key_id
           )
    AND NOT EXISTS (
            SELECT *
             FROM media_streams
            WHERE chroma_key_id = OLD.chroma_key_id
           );
END;

These triggers ensure that whenever a row is deleted from the media or media_streams tables, the corresponding chroma_key is checked to see if it is still referenced by any other rows. If not, the chroma_key is deleted.

Creating After-Update Triggers

In addition to after-delete triggers, you may also need after-update triggers to handle cases where a row in a child table is updated to reference a different row in the parent table. This could leave the previously referenced row in the parent table orphaned if it is no longer referenced by any other rows.

For example, if a row in the media table is updated to reference a different chroma_key, the previously referenced chroma_key should be checked to see if it is still needed.

CREATE TRIGGER media_after_update_1 AFTER UPDATE OF chroma_key_id ON media
WHEN OLD.chroma_key_id IS NOT NEW.chroma_key_id
BEGIN
  DELETE FROM chroma_keys
   WHERE id = OLD.chroma_key_id
    AND NOT EXISTS (
            SELECT *
             FROM media
            WHERE chroma_key_id = OLD.chroma_key_id
           )
    AND NOT EXISTS (
            SELECT *
             FROM media_streams
            WHERE chroma_key_id = OLD.chroma_key_id
           );
END;

CREATE TRIGGER media_streams_after_update_1 AFTER UPDATE OF chroma_key_id ON media_streams
WHEN OLD.chroma_key_id IS NOT NEW.chroma_key_id
BEGIN
  DELETE FROM chroma_keys
   WHERE id = OLD.chroma_key_id
    AND NOT EXISTS (
            SELECT *
             FROM media
            WHERE chroma_key_id = OLD.chroma_key_id
           )
    AND NOT EXISTS (
            SELECT *
             FROM media_streams
            WHERE chroma_key_id = OLD.chroma_key_id
           );
END;

These triggers ensure that whenever a row in the media or media_streams tables is updated to reference a different chroma_key, the previously referenced chroma_key is checked to see if it is still needed. If not, it is deleted.

Using Use-Counters as an Alternative to Triggers

While triggers provide a robust solution to the problem of orphaned rows, they can be complex to implement and maintain, especially in databases with many tables and relationships. An alternative approach is to use use-counters to track the number of references to each row in the parent table.

Implementing Use-Counters

A use-counter is a column in the parent table that keeps track of how many rows in the child tables reference it. For example, you could add a use_count column to the chroma_keys table. Each time a row in a child table references a chroma_key, the use_count is incremented. Each time a reference is removed, the use_count is decremented. When the use_count reaches zero, the chroma_key can be deleted.

To implement this, you would need to create triggers on the child tables to update the use_count column whenever a row is inserted, updated, or deleted.

CREATE TRIGGER media_after_insert_1 AFTER INSERT ON media
BEGIN
  UPDATE chroma_keys
   SET use_count = use_count + 1
   WHERE id = NEW.chroma_key_id;
END;

CREATE TRIGGER media_after_delete_1 AFTER DELETE ON media
BEGIN
  UPDATE chroma_keys
   SET use_count = use_count - 1
   WHERE id = OLD.chroma_key_id;
  DELETE FROM chroma_keys
   WHERE use_count = 0;
END;

CREATE TRIGGER media_after_update_1 AFTER UPDATE OF chroma_key_id ON media
WHEN OLD.chroma_key_id IS NOT NEW.chroma_key_id
BEGIN
  UPDATE chroma_keys
   SET use_count = use_count - 1
   WHERE id = OLD.chroma_key_id;
  UPDATE chroma_keys
   SET use_count = use_count + 1
   WHERE id = NEW.chroma_key_id;
  DELETE FROM chroma_keys
   WHERE use_count = 0;
END;

These triggers ensure that the use_count column is always up-to-date and that any chroma_key with a use_count of zero is deleted.

Advantages and Disadvantages of Use-Counters

The use-counter approach has several advantages over the trigger-based approach. First, it is simpler to implement and maintain, especially in databases with many tables and relationships. Second, it avoids the need for complex queries to check for orphaned rows, which can be expensive in terms of performance.

However, the use-counter approach also has some disadvantages. First, it requires additional storage for the use_count column. Second, it introduces the possibility of errors if the use_count column is not updated correctly, which could lead to orphaned rows or incorrect deletions. To mitigate this risk, you should implement periodic sanity checks to ensure that the use_count values are correct.

Periodic Maintenance as a Fallback Solution

In some cases, the complexity and performance overhead of triggers or use-counters may be too high, especially if the data is not critical and can tolerate some delay in cleaning up orphaned rows. In such cases, a periodic maintenance procedure can be used as a fallback solution.

Implementing Periodic Maintenance

A periodic maintenance procedure involves running a script at regular intervals (e.g., daily, weekly, or monthly) to identify and delete orphaned rows. This script can be implemented in SQL or a programming language like TypeScript, depending on your preferences and the complexity of the database.

For example, you could implement a springClean function in TypeScript that deletes orphaned rows from the chroma_keys table:

const deleteOrphaned = (db: Database.Database, table: string): void => {
  const rows = db.prepare<[string]>(`
    SELECT s.name AS fromTable, f."from" AS column
    FROM sqlite_schema AS s
    JOIN pragma_foreign_key_list(s.name) AS f
    WHERE s.type = 'table' AND f."table" = ?
  `).all(table) as { fromTable: string, column: string }[];
  
  const where = rows.map(({ fromTable, column }) => (
    `NOT EXISTS (SELECT * FROM ${fromTable} WHERE ${column} = t.id)`
  )).join(' AND ');
  
  const { changes } = db.prepare(
    `DELETE FROM "${table}" AS t WHERE ${where}`,
  ).run();
  
  logger.log(`[sql] Deleted ${changes} orphaned ${table}`);
};

const springClean = (db: Database.Database) => {
  /* ... */
  deleteOrphaned(db, 'chromaKeys');
  deleteOrphaned(db, 'ttss');
  /* ... */
};

This function identifies all foreign key references to the specified table and constructs a query to delete any rows that are not referenced by any other table. The springClean function can then be called at regular intervals to clean up orphaned rows.

Advantages and Disadvantages of Periodic Maintenance

The periodic maintenance approach has several advantages. First, it is simple to implement and does not require any changes to the database schema or triggers. Second, it can be run at a time when the database is not under heavy load, minimizing the impact on performance.

However, the periodic maintenance approach also has some disadvantages. First, it does not provide real-time enforcement of the business rule, meaning that orphaned rows may exist in the database for some time before they are cleaned up. Second, it requires additional effort to schedule and run the maintenance procedure, which may not be feasible in all environments.

Conclusion: Choosing the Right Solution for Your Database

Preventing orphaned rows in an entity-component system is a complex problem that requires careful consideration of the trade-offs between different solutions. Triggers provide a robust and real-time solution but can be complex to implement and maintain. Use-counters offer a simpler alternative but require additional storage and periodic sanity checks. Periodic maintenance is a fallback solution that is easy to implement but does not provide real-time enforcement.

The best solution for your database will depend on your specific requirements, including the complexity of your schema, the criticality of the data, and the performance constraints of your environment. By understanding the strengths and weaknesses of each approach, you can make an informed decision and implement a solution that ensures the integrity and efficiency of your database.