How Can I Prevent Empty String Insertion into MySQL Tables Without Application-Side Checks?

Preventing Empty String Insertion in MySQL with Constraints

In this query, the user has successfully prevented the insertion of NULL values into a database table. However, they also want to prevent the insertion of empty strings without relying solely on application-side checks.

To achieve this using a database constraint, we can add a CHECK constraint to the table definition:

CREATE TABLE IF NOT EXISTS tblFoo (
  foo_id int(11) NOT NULL AUTO_INCREMENT,
  foo_test VARCHAR(50) NOT NULL CHECK (foo_test  ''),
  PRIMARY KEY (foo_id)
);

This constraint ensures that the foo_test column cannot contain empty strings. However, it's important to note that in MySQL versions prior to 8.0, CHECK constraints are parsed but ignored by all storage engines. Therefore, an empty string can still be inserted into the table.

As a workaround, the user can consider using triggers to enforce the constraint:

CREATE TRIGGER trg_tblFoo_prevent_empty_string
BEFORE INSERT ON tblFoo
FOR EACH ROW
BEGIN
  IF NEW.foo_test = '' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Empty string not allowed in foo_test column';
  END IF;
END;

This trigger will raise an error and prevent the insertion of an empty string into the foo_test column.

The above is the detailed content of How Can I Prevent Empty String Insertion into MySQL Tables Without Application-Side Checks?. For more information, please follow other related articles on the PHP Chinese website!