Preventing Main Topic Duplication in SQLite Topic-Subtopic Relationships

Enforcing Main Topic and Non-Main Topic Constraints in SQLite

When designing a database schema to manage topics and subtopics, one common challenge is ensuring that a main topic cannot be duplicated as a non-main topic for the same record. This issue arises when you have a hierarchical or relational structure where each record (e.g., a task or todo) is associated with a main topic and optionally with non-main topics. The goal is to enforce a constraint that prevents a main topic from being listed as a non-main topic for the same record, while still allowing flexibility in associating multiple non-main topics.

For example, consider a scenario where you have a topics table with entries like 1 Work, 2 Family, 3 Friends, and 4 Hobbies. A record with id 17 might have Work as its main topic and Family and Hobbies as non-main topics. However, it should not be permissible to list Work as both the main topic and a non-main topic for the same record. This constraint ensures data integrity and prevents logical inconsistencies in the database.

The core of the problem lies in designing a schema and implementing constraints that enforce this rule. SQLite, being a lightweight database, does not natively support complex constraints that span multiple tables or require joins to enforce. Therefore, the solution must rely on a combination of schema design, foreign key constraints, and triggers to enforce the desired behavior.

Schema Design and Foreign Key Constraints

The first step in addressing this issue is to design a schema that clearly separates main topics from non-main topics while maintaining referential integrity. A well-designed schema will include the following tables:

1. Topics Table: This table stores all available topics, each with a unique identifier (id) and a name.
2. Records Table: This table stores the main records (e.g., tasks or todos), each with a unique identifier (id) and a reference to its main topic (mainTopicID).
3. Link Table: This table associates records with their non-main topics, using foreign keys to reference both the record and the topic.

Here is an example schema:

CREATE TABLE Topics (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE Records (
    id INTEGER PRIMARY KEY,
    mainTopicID INTEGER NOT NULL REFERENCES Topics(id)
);

CREATE TABLE Link (
    recordID INTEGER NOT NULL REFERENCES Records(id),
    topicID INTEGER NOT NULL REFERENCES Topics(id),
    PRIMARY KEY (recordID, topicID)
);

In this schema:

• The Topics table stores all possible topics.
• The Records table stores each record and its associated main topic.
• The Link table stores the relationships between records and their non-main topics.

Foreign key constraints ensure that:

• Every mainTopicID in the Records table must exist in the Topics table.
• Every recordID and topicID in the Link table must exist in the Records and Topics tables, respectively.

However, this schema alone does not prevent a main topic from being listed as a non-main topic for the same record. To enforce this constraint, we need to implement triggers.

Implementing Triggers to Enforce Constraints

Triggers in SQLite allow you to automatically execute logic when certain events occur, such as inserting or updating data. In this case, we can use triggers to enforce the constraint that a main topic cannot be listed as a non-main topic for the same record.

Insert Trigger for the Link Table

The first trigger ensures that when a new entry is added to the Link table, the topicID does not match the mainTopicID of the corresponding record in the Records table.

CREATE TRIGGER PreventMainTopicAsNonMain
BEFORE INSERT ON Link
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Main topic cannot be a non-main topic')
    WHERE EXISTS (
        SELECT 1
        FROM Records
        WHERE id = NEW.recordID AND mainTopicID = NEW.topicID
    );
END;

This trigger checks if the topicID being inserted into the Link table matches the mainTopicID of the corresponding record in the Records table. If a match is found, the trigger raises an error and prevents the insertion.

Update Trigger for the Link Table

Similarly, an update trigger ensures that when an entry in the Link table is updated, the new topicID does not match the mainTopicID of the corresponding record.

CREATE TRIGGER PreventMainTopicAsNonMain_Update
BEFORE UPDATE ON Link
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Main topic cannot be a non-main topic')
    WHERE EXISTS (
        SELECT 1
        FROM Records
        WHERE id = NEW.recordID AND mainTopicID = NEW.topicID
    );
END;

This trigger performs the same check as the insert trigger but applies to updates.

Delete Trigger for the Topics Table

To maintain referential integrity, a delete trigger ensures that a topic cannot be deleted if it is still referenced in either the Records or Link tables.

CREATE TRIGGER PreventTopicDeletion
BEFORE DELETE ON Topics
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Topic is in use and cannot be deleted')
    WHERE EXISTS (
        SELECT 1
        FROM Records
        WHERE mainTopicID = OLD.id
    ) OR EXISTS (
        SELECT 1
        FROM Link
        WHERE topicID = OLD.id
    );
END;

This trigger checks if the topic being deleted is referenced in either the Records or Link tables. If it is, the trigger raises an error and prevents the deletion.

Update Trigger for the Topics Table

An update trigger ensures that a topic’s id cannot be changed if it is still referenced in either the Records or Link tables.

CREATE TRIGGER PreventTopicIDChange
BEFORE UPDATE OF id ON Topics
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'Topic ID is in use and cannot be changed')
    WHERE EXISTS (
        SELECT 1
        FROM Records
        WHERE mainTopicID = OLD.id
    ) OR EXISTS (
        SELECT 1
        FROM Link
        WHERE topicID = OLD.id
    );
END;

This trigger checks if the topic’s id is referenced in either the Records or Link tables. If it is, the trigger raises an error and prevents the update.

Alternative Schema Design: Denormalizing Main Topics

An alternative approach to enforcing the constraint is to denormalize the schema by including the main topic in the Link table and using a flag to distinguish between main and non-main topics. This approach simplifies the schema but requires additional logic to ensure that each record has exactly one main topic.

Here is an example schema using this approach:

CREATE TABLE Topics (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE Records (
    id INTEGER PRIMARY KEY
);

CREATE TABLE RecordTopics (
    recordID INTEGER NOT NULL REFERENCES Records(id),
    topicID INTEGER NOT NULL REFERENCES Topics(id),
    isMain INTEGER NOT NULL CHECK (isMain IN (0, 1)),
    PRIMARY KEY (recordID, topicID)
);

In this schema:

• The RecordTopics table includes an isMain flag to indicate whether a topic is the main topic for a record.
• A unique index ensures that each record has at most one main topic.

CREATE UNIQUE INDEX OneMainTopicPerRecord
ON RecordTopics(recordID)
WHERE isMain = 1;

Triggers for the Denormalized Schema

To enforce the constraint that each record has exactly one main topic, we can use triggers to automatically manage the isMain flag.

Insert Trigger for the RecordTopics Table

This trigger ensures that when a new entry is added to the RecordTopics table with isMain = 1, any existing main topic for the same record is demoted to isMain = 0.

CREATE TRIGGER EnsureOneMainTopic_Insert
AFTER INSERT ON RecordTopics
FOR EACH ROW
WHEN NEW.isMain = 1
BEGIN
    UPDATE RecordTopics
    SET isMain = 0
    WHERE recordID = NEW.recordID
    AND topicID != NEW.topicID
    AND isMain = 1;
END;

Update Trigger for the RecordTopics Table

This trigger ensures that when an entry in the RecordTopics table is updated to isMain = 1, any existing main topic for the same record is demoted to isMain = 0.

CREATE TRIGGER EnsureOneMainTopic_Update
AFTER UPDATE ON RecordTopics
FOR EACH ROW
WHEN NEW.isMain = 1
BEGIN
    UPDATE RecordTopics
    SET isMain = 0
    WHERE recordID = NEW.recordID
    AND topicID != NEW.topicID
    AND isMain = 1;
END;

Delete Trigger for the RecordTopics Table

This trigger ensures that when a main topic is deleted, the next oldest topic for the same record is promoted to isMain = 1.

CREATE TRIGGER PromoteNextMainTopic_Delete
AFTER DELETE ON RecordTopics
FOR EACH ROW
WHEN OLD.isMain = 1
BEGIN
    UPDATE RecordTopics
    SET isMain = 1
    WHERE recordID = OLD.recordID
    AND topicID = (
        SELECT topicID
        FROM RecordTopics
        WHERE recordID = OLD.recordID
        ORDER BY topicID
        LIMIT 1
    );
END;

Conclusion

Enforcing constraints that prevent a main topic from being listed as a non-main topic for the same record in SQLite requires careful schema design and the use of triggers. By separating main topics from non-main topics and using triggers to enforce constraints, you can ensure data integrity and prevent logical inconsistencies in your database. Whether you choose to normalize or denormalize your schema depends on your specific use case and performance considerations. Both approaches have their trade-offs, but with the right combination of schema design and triggers, you can achieve the desired behavior in SQLite.