My-Little-Forum / mylittleforum

A simple PHP and MySQL based internet forum that displays the messages in classical threaded view (tree structure)
GNU General Public License v3.0
122 stars 48 forks source link

Remove keyword UNIQUE from definitions for user_name and user_email in mlf2_userdata #631

Closed auge8472 closed 2 years ago

auge8472 commented 2 years ago

The uniqueness of these columns is ensured by the key definitions at the end of the query.

This is the hopefully last occurence of the doubled key definitions for the columns user_name and user_email in the table mlf2_userdata, what kept me occupied as an aside of #619. Not only the there solved problem with updates of the table structure led to several identical keys/indexes for these columns, also the table definition in the installation script doubled the keys with different names.

CREATE TABLE mlf2_userdata (
 user_id int(11) NOT NULL AUTO_INCREMENT,
 …,
 user_name varchar(128) NOT NULL UNIQUE COLLATE utf8mb4_bin,
 …,
 user_email varchar(255) character set utf8 NOT NULL UNIQUE,
 …,
 UNIQUE KEY key_user_name (user_name),
 UNIQUE KEY key_user_email (user_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

The keyword UNIQUE in the column definitions created the outdated keys user_name and user_email for the eponymous columns. The definitions of UNIQUE KEY … created the additional and desired keys key_user_name and key_user_email. Thatswhy I kept the definitions of UNIQUE KEY … and removed the keyword UNIQUE from the column definitions.