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

General discussion of development ideas and the approaches taken in the 3.x branch of phpBB. The next feature release of phpBB 3 will be 3.3/Proteus.
Forum rules
Please do not post support questions regarding installing, updating, or upgrading phpBB 3.1. If you need support for phpBB 3.1 please visit the 3.1.x Support Forum on phpbb.com.

If you have questions regarding writing extensions please post in Extension Writers Discussion to receive proper guidance from our staff and community.
Post Reply
thenickdude
Registered User
Posts: 3
Joined: Sat Nov 23, 2013 1:47 pm

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

Post by thenickdude » Sat Nov 23, 2013 1:58 pm

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?

User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 557
Joined: Thu Sep 07, 2006 11:22 am
Location: Karlsruhe, Germany
Contact:

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

Post by bantu » Sun Nov 24, 2013 12:41 am

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
Registered User
Posts: 3
Joined: Sat Nov 23, 2013 1:47 pm

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

Post by thenickdude » Sun Nov 24, 2013 6:24 am

Thanks bantu, I'll get some tickets submitted.

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

User avatar
VSE
Extension Customisations
Extension Customisations
Posts: 670
Joined: Mon Mar 08, 2010 9:18 am

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

Post by VSE » Sun Nov 24, 2013 6:36 am

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)
Has an irascible disposition.

thenickdude
Registered User
Posts: 3
Joined: Sat Nov 23, 2013 1:47 pm

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

Post by thenickdude » Sun Nov 24, 2013 6:41 am

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
Registered User
Posts: 304
Joined: Mon Feb 08, 2010 3:42 am

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

Post by Danielx64 » Sun Nov 24, 2013 10:56 am

Would this igo into 3.0 or 3.1?

User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 557
Joined: Thu Sep 07, 2006 11:22 am
Location: Karlsruhe, Germany
Contact:

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

Post by bantu » Mon Nov 25, 2013 10:38 am

Danielx64 wrote: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.


Post Reply