This is what I meant by a redundant unique index, a field which is defined as primary key is unique by definition, so the UNIQUE KEY constraint is superfluous.
This is also found in some other tables.
A potential more severe problem is:
CREATE TABLE `user_privilege_users` (
`upriv_users_id` smallint(5) NOT NULL AUTO_INCREMENT,
`upriv_users_uacc_fk` int(11) NOT NULL,
`upriv_users_upriv_fk` smallint(5) NOT NULL,
PRIMARY KEY (`upriv_users_id`),
First, I do not see the added value of an auto increment key in a link table. The combination of both FK field is already unique and perfectly suitable as primary key.
Worse, the upriv_users_id PK field, if used, should not be a smallint. One of the foreign key fields refers to the User Account table and because each user can have more privileges, the potential number of entries of this link table is higher than the number of users.
So IF a separate auto-increment PK field is used, it should at least be as big as the biggest of the FK fields, so it ahould be an INT and surely not a SMALLINT.
