Why some relational tables don't have composite primary keys?

[thenickdude]

Hey everyone,

I was trying to sync some tables between master and slave on MySQL/InnoDB using pt-table-sync, when I noticed something odd. There are several tables that relate users to some other entity, that don't have any primary key defined. This means that pt-table-sync has real trouble identifying rows distinctly, because there can be duplicates.

For example phpbb_user_group is defined as:

Code: Select all

CREATE TABLE phpbb_user_group (
	group_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
	user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
	group_leader tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
	user_pending tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
	KEY group_id (group_id),
	KEY user_id (user_id),
	KEY group_leader (group_leader)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

You can see that there is no primary or unique key defined for the table!

It seems to me that, since a user shouldn't be able to be in a group twice, (user_id, group_id) should be the primary key of this table. If I run this query to check for duplicate rows:

Code: Select all

SELECT group_id, user_id, COUNT(*) 
FROM phpbb_user_group 
GROUP BY group_id, user_id
HAVING COUNT(*) > 1;

It finds 712 duplicates that shouldn't be there (and which a primary key would have excluded) within a database of 560,000 users. Luckily, there aren't any inconsistencies within those groups, which might cause inconsistent handling of leadership or pending status, because I get no results from this query:

Code: Select all

SELECT group_id, user_id, COUNT(*) 
FROM phpbb_user_group 
GROUP BY group_id, user_id
HAVING COUNT(DISTINCT group_leader) > 1 OR COUNT(DISTINCT user_pending) > 1;

Is there a reason why there is no primary key defined on this table, or is just because of backwards compatibility issues?

[bantu]

To me this just sounds like a bug that should be fixed. Feel free to have a look at the other tables as well.

[thenickdude]

Thanks bantu, I'll get some tickets submitted.

EDIT: Would you prefer one ticket per table, or one big ticket for all instances?

[VSE]

The table "phpbb_user_group" can not have unique group_id and user_id fields.

That table holds the relationships between groups and users. So if user_id 1 is a member of 3 groups, they would appear in three rows (once for each group). And of course, group ids will constantly appear over and over as each group will have multiple users.

ie:

Code: Select all

group_id  user_id
2            1          (user 1 is a member of group 2)
4            1          (user 1 is a member of group 4)
5            1          (user 1 is a member of group 5)
5            2          (user 2 is a member of group 5)
5            3          (user 3 is a member of group 5)
2            4          (user 4 is a member of group 2)
5            4          (user 4 is a member of group 5)

[thenickdude]

Yes, of course. My suggestion is that (user_id, group_id) should be the primary key of that table. That allows for the situation you describe, without allowing nonsense situations like (user_id, group_id) VALUES (1, 2), (1, 2) to develop.

[Danielx64]

Would this igo into 3.0 or 3.1?

[bantu]

Would this igo into 3.0 or 3.1?

Considering that it has been like that since forever, I would say that 3.1 is fine.

[keith10456]

@ thenickdude - Good catch