Prevent spaces, null values, and invalid dates in MySQL: What can I do?

Question

user table

CREATE TABLE `USERS` (
 `ID` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `NAME` char(255) COLLATE utf8_unicode_ci NOT NULL,
 `EMAIL` char(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `CREATED_IN` datetime NOT NULL,
 `SIGNED_IN` datetime NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

trigger

CREATE TRIGGER `ABC` BEFORE INSERT ON `USERS` FOR EACH ROW
IF NEW.ID = "" OR NEW.NAME = "" OR
NEW.CREATED_IN = "" OR NEW.CREATED_IN = "0000-00-00 00:00:00" OR
NEW.SIGNED_IN = "" OR NEW.SIGNED_IN = "0000-00-00 00:00:00"
THEN SIGNAL SQLSTATE "45000"; END IF

ID column

• How to make it not accept null values? ("", " ", " ", " ", etc...) -> should return an error
• If there is already a value, how to remove all spaces in it? ("bf9 d 34 c9 08" = "bf9d34c908")

Name column

• How to make it not accept null values? ("", " ", " ", " ", etc...) -> should return an error

Email Column

• How to make it not accept null values? ("", " ", " ", " ", etc...) -> should return an error
• If there is already a value, how to remove all spaces in it? ("nkr owks lpehqp jmgdb @ gm ail.com" = "[email protected]")

CREATED_IN and SIGNED_IN columns

• How to make it not accept invalid values? When inserting an invalid value such as a letter, number, or invalid format, an error should be returned instead of entering 0000-00-00 00:00:00.

MySQL version is 5.7

What is the best way to handle these requirements on the USERS table?

Answer 1

A trigger can look like this:

CREATE TRIGGER process_input
BEFORE INSERT ON USERS
FOR EACH ROW
BEGIN
    SET NEW.id = REPLACE(NEW.id, ' ', '');
    IF NEW.id = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`id` cannot be empty.';
    END IF;

    SET NEW.name = TRIM(NEW.name);
    IF NEW.name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = ''name` cannot be empty.';
    END IF;

    SET NEW.email = REPLACE(NEW.email, ' ', '');
    IF NEW.email = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '`email` cannot be empty.';
    END IF;

    IF STR_TO_DATE(created_in, '%Y-%m-%d %H:%i:%s') IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Incorrect `created_in` datetime value.';
    END IF;

    IF STR_TO_DATE(signed_in, '%Y-%m-%d %H:%i:%s') IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Incorrect `signed_in` datetime value.';
    END IF;
END

NOTE - This trigger allows any value to be NULL (but only email can be NULL, all other columns are defined as NOT NULL).

You can rearrange the blocks and put the condition with the highest probability first. The trigger will not execute the following code after executing SIGNAL.