Fun SQL Tricks II

Last time, I outlined some project requirements and a solution to one of the requirements, that of object IDs unique across first class objects in the system. This involved foreign key constraints, a stored function, character sets, collations, and a trigger, and also showed a use for SELECT…RETURNING. This time, I’m going to look at the other requirements specific to user objects.

Preventing Deletion

There are a couple of ways to prevent deletion of rows in a table. First, foreign key constraints in other tables referencing this table can prevent rows from being deleted. It is almost certain such constraints will exist in the future of the project, but they do not currently. And even if they did, that still doesn’t guarantee a user object cannot be deleted. So that leaves us with a question. Is there a way to prevent the deletion of rows from a table? As it happens, MariaDB has triggers that can run before deletion and we can use those. So problem solved, right?

Well, mostly. It turns out that TRUNCATE TABLE does not trigger the deletion triggers. This is unfortunate, but there isn’t anything we can do about that. We just have to work with what we do have.

Here’s what that trigger looks like:

DELIMITER //
CREATE OR REPLACE TRIGGER audit_user_delete
    BEFORE DELETE ON audit_users FOR EACH ROW
    CALL lwas_nodelete();//
CREATE OR REPLACE PROCEDURE lwas_nodelete() DETERMINISTIC NO SQL
    BEGIN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deletion of object not allowed';
    END;//
DELIMITER ;

Here, I have a stored procedure (lwas_nodelete) whose sole purpose is to raise an error. While I could just put the SIGNAL statement in place of the CALL in the trigger definition, having a separate procedure allows me to have a consistent message everywhere that can be updated if required.

How this works should be obvious. If an error is raised before the delete happens, it will abort the whole operation and no rows are removed.

The same idea can be applied to the object_ids table from last time, as well. And we can also apply the same thing to updates on the object_ids table:

DELIMITER //
CREATE OR REPLACE PROCEDURE lwas_noupdate() DETERMINISTIC NO SQL
    BEGIN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Update of object not allowed';
    END;//
CREATE OR REPLACE TRIGGER object_ids_update
    BEFORE UPDATE ON object_ids FOR EACH ROW
    CALL lwas_nomodify();//
CREATE OR REPLACE TRIGGER object_ids_delete
    BEFORE DELETE ON object_ids FOR EACH ROW
    CALL lwas_nodelete();//
DELIMITER ;

It should be fairly obvious how that works. It is worth noting that blocking deletes of object ID records is almost certainly redundant in the face of foreign key checks from other tables. However, this gives an extra defence against removing records that are meant to be kept indefinitely.

Login Name Uniqueness

Now for the requirement about login name uniqueness. Because the login name, as stored in the user_login column, is only required to be unique among the users flagged as “active” in the user_status column, there is a bit of a conundrum. Since duplicates would be allowed in the user_login column as long as the duplicate rows are all flagged as “inactive”, a unique index does no good. Further, there isn’t any other column that can be used in a compound index to accomplish the same thing. That means a method of raising an error whenever an insertion or modification of a row in the table that would create a duplicate active user occurs.

This time, two triggers are required. One that will be attached to the after insert event and the other will be attached to the before update event. These triggers look as follows:

DELIMITER //
CREATE OR REPLACE TRIGGER user_create2
    AFTER INSERT ON users FOR EACH ROW
    BEGIN
        DECLARE c INT;
        IF NEW.user_status = 'active' THEN
            SELECT COUNT(user_id) FROM users WHERE
                user_login = NEW.user_login
                AND user_id <> NEW.user_id
                AND user_status = 'active'
                INTO c;
            IF c > 0 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attempt to add active user login name that already exists';
            END IF;
        END IF;
    END;//
CREATE OR REPLACE TRIGGER user_update1
    BEFORE UPDATE ON users FOR EACH ROW
    BEGIN
        DECLARE c INT;
        IF NEW.user_id <> OLD.user_id THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Changing permanent ID of a user is not allowed';
        END IF;
        IF NEW.user_status = 'active' THEN
            SELECT COUNT(user_id) FROM users WHERE
                user_login = NEW.user_login
                AND user_id <> NEW.user_id
                AND user_status = 'active'
                INTO c;
            IF c > 0 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attempt to update active user with a duplicate login name';
            END IF;
        END IF;
    END;//
DELIMITER ;

Those should be straight forward enough. You may note the declaration of variables within the trigger routines and the INTO clause for SELECT to put the result of the SELECT into a variable. Also, in case it wasn’t obvious, NEW and OLD refer to the new row contents and previous row contents respectively.

You’ll note that the update trigger also checks for modification of the user_id column. This is because the IDs are permanent and changing those could cause a data integrity problem. It is also something that cannot be fully covered by a foreign key constraint on the user_id since that would allow changing the ID to that of some other object type.

Conclusion

As you can see, triggers and foreign key constraints can be useful tools to enforce data integrity. The best part is that they operate for any database user without reliance on application code.

Next time, assuming I get around to it, I’ll look at the auditing requirement outlined last time.