Implementation of prohibiting modification of specific columns in MySQL

During the database design process, sometimes we need to ensure that the data in certain columns is not modified to protect the consistency and integrity of the data. MySQL databases provide a variety of ways to achieve this, including using Triggers. This article will explain in detail how to prevent modification of specific columns in MySQL database tables by creating triggers.

introduction

In practical applications, we often encounter situations where we need to protect certain key columns in the table from being updated. For example, fields such as user ID, creation time, etc. should not be modified once set. While this rule can be enforced through application logic, protection at the database level can provide greater security and consistency.

Use of triggers

A trigger is a special type of stored procedure provided by MySQL that is automatically executed before or after an INSERT, UPDATE, or DELETE operation occurs on a specified table. In this case, we will useBEFORE UPDATETriggers to check and block modifications of specific columns before data is updated.

Example

Suppose we have a name calledusersThe table contains the following columns:id(User ID),name(username),email(email address) andcreated_at(Creation time). We want to ensureidandcreated_atThe list is not updated under any circumstances.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 1: Create a trigger

To prohibit modificationidandcreated_atcolumns, we need tousersCreate a tableBEFORE UPDATEtrigger. Here is the SQL code to create the trigger:

DELIMITER $$

CREATE TRIGGER before_users_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    -- Modification is prohibited id List
    IF  <>  THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update the id column';
    END IF;

    -- Modification is prohibited created_at List
    IF NEW.created_at <> OLD.created_at THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update the created_at column';
    END IF;
END$$

DELIMITER ;

In this trigger, we useSIGNALStatement to throw an error if attempted to updateidorcreated_atList.SIGNALStatements allow us to customize error codes and error messages, making errors easier to identify and process.

Step 2: Test the trigger

Now, we can try to updateusersSome records in the table, especially trying to modifyidandcreated_atcolumn to see if the trigger works as expected.

Try updatingidList:

UPDATE users SET id = 100, name = 'John Doe' WHERE id = 1;

When executing this command, you will receive an error message saying "Cannot update the id column".

Try updatingcreated_atList:

UPDATE users SET created_at = NOW(), name = 'Jane Doe' WHERE id = 1;

Similarly, you will receive an error message saying "Cannot update the created_at column".

in conclusion

By using MySQL's triggers, we can effectively prohibit modifying specific columns in database tables. This approach not only simplifies application logic, but also provides stronger data integrity protection. In practical applications, corresponding triggers can be created for different tables and columns as needed to ensure data consistency and security.

This is the article about the implementation of prohibiting the modification of specific columns of database tables in MySQL. For more related contents of prohibiting the modification of specific columns of MySQL tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!