Firstly, just a quick note. I have searched for an answer but I can only people asking why rather than how to overcome.
I am currently designing a set of database tables for a project I am working on. A couple of the 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) ) ENGINE = INNODB, CHARACTER SET utf8mb4, COLLATE utf8mb4_general_ci;
I would like to ensure that the email remains unique however I am unable to use the unique constraint due to the length of the field and the fact I am using the utf8mb4 character set (trying to do so results in an error sch as "Specified key was too long; max key length is 767 bytes").
To overcome this I appear to have the following choices:
- Modify the character set to 1 that will store the data in with a smaller footprint
- Reduce the length of the field so it fits within the limit
- Split the email into 2 sections (username, domain) and use these as unique values
- Check for uniqueness in the code utilizing the database
- Create a hash of the email field and store this in another field. Use this for unique constraint
- Use a before insert and before update trigger to enforce the uniqueness
I am not overly keen on options 1 (or am I simply, in this case, 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 following the rules of the RFC's) as I do not believe the structure of the database should be compromised to satisfy a simple constraint.
4 relies on the code to ensure the database is conforming. I am not overly keen on this as it can cause the data stored to lose integrity is someone forgets to check.
5 would require extraneous data to be stored in the database. This could cause collisions but dependent upon the hashing algorithm it may not be a problem.
6 would be implemented something like:
DELIMITER $$ CREATE DEFINER = 'user'@'localhost' TRIGGER MemberBeforeInsertTrigger BEFORE INSERT ON members FOR EACH ROW BEGIN DECLARE userCount int; SELECT COUNT(email) INTO userCount FROM members WHERE email = new.email; -- Ensure Email is not in use. IF userCount > 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email address already in use'; END IF; END $$ DELIMITER ;
Would using a trigger such as this cause any negative affects in the performance of the database (compared with above options) or am I simply overthinking this?