First of all, just a quick note. I've looked for an answer but I can only ask why rather than know how to overcome.
I am currently designing a set of database tables for a project I am working on. Some tables such as the following (simplified table):
CREATE TABLE Members id int (11) NOT NULL AUTO_INCREMENT email varchar (255) NOT NULL PRIMARY KEY (id) ) MOTOR = INNODB, CHARACTER SET utf8mb4, COLLATE utf8mb4_general_ci;
I would like to make sure the e-mail stays unique, but I can not use this unique constraint because of the length of the field and the fact that I'm using the utf8mb4 character set (this attempt generates a sch error such that "specified key" was too long, the maximum length of the key is 767 bytes ").
To overcome this, I seem to have the following choices:
- Change the character set to 1 which will store the data with a smaller footprint
- Reduce the length of the field so that it fits within the limits
- Split the email into 2 sections (user name, domain) and use them as unique values
- Check the uniqueness of the code using the database
- Create a hash of the email field and store it in another field. Use this for a single constraint
- Use a trigger before insert and before update to apply uniqueness
I'm not particularly interested in options 1 (or simply, in this case, I'm using the wrong character set?), 2 (even this field is actually too short for a valid email address according to the RFC) and 3 (the domain would still be too long if I followed the rules of the RFC) because I do not think that the structure of the database should be compromised to satisfy a simple constraint.
4 s' press the code to make sure the database is compliant. I am not very enthusiastic about this as this can result in loss of integrity of stored data if someone forgets to check it.
5 would require unnecessary data to be stored in the database. This could cause collisions but, depending on the hash algorithm, this might not be a problem.
6 would be implemented something like:
DELIMITE $$ CREATE DEFINING = & # 39; user & # 39; @ & # 39; localhost & # 39; TRIGGER MemberBeforeInsertTrigger BEFORE INSERTING ON Members FOR EACH ROW TO START DECLARE userCount int; TO SELECT COUNT (email) IN userCount Members O email = new.email; - Make sure the email is not used. IF userCount> 0 THEN SQLSTATE SIGNAL> 45000 & # 39; SET MESSAGE_TEXT = & # 39; Email address already used & # 39 ;; END IF; END $$ DELIMITER;
Would using a trigger like this have a negative effect on the performance of the database (compared to the options above) or am I just thinking too much about this?