Do I need a composite primary key in a junction table?

Question

I have a junction table like this:

CREATE TABLE `entitytagjunction` (
     `EntityId` bigint(20) NOT NULL,
     `TagId` bigint(20) NOT NULL,

     PRIMARY KEY (`EntityId`,`TagId`),
     KEY `IX_EntityId` (`EntityId`) USING HASH,
     KEY `IX_TagId` (`TagId`) USING HASH,

     CONSTRAINT `FK_EntityTagJunction_Entities_EntityId` 
        FOREIGN KEY (`EntityId`) REFERENCES `entities` (`Id`) 
        ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `FK_EntityTagJunction_Tags_TagId` 
        FOREIGN KEY (`TagId`) REFERENCES `tags` (`Id`) 
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have EntityId and TagId as composite primary key to ensure uniqueness, and SQL Server and MySql will both use the primary key as clusteredId.

As far as I could read from the other posts, Guid is not good to be a primary key because the randomness make fragments for clusteredIds, poor for performance if the table consist of millions of rows. Apparently the combination of EntityId and TagId has some randomness. Will this have impact on performance when using them as a composite primary key and clusteredId by default? or should I add an int column as a surrogate primary key though it will not be used in applications?

Answer 1

You should use the combination of EntityId and TagId as composite primary key, here is why:

1. This will enforce uniqueness, that you will have to do even if you add a surrogate key.

2. These columns are what you will base most if not all of your queries on this table, so they should be indexed anyway

3. Adding a surrogate key will not have any benefit from the performance perspective.

btw, I don't know about MySql, but in Sql Server, a primary key is not necessarily the clustered index. It is set as the clustered index by default, but you can change the clustered index to any other index if you need.

Answer 2

If entitytagjunction is a simple junction table that is never referenced, then you can make the two columns a primary key. This solves the problem of creating a unique index on the two columns.

If other tables could be referring to a row in entitytagjunction, then don't use a composite primary key. Composite primary keys just complicate foreign key references, SQL queries, and can sometimes become unmaintainable.

I strongly prefer auto-incremented int or bigint as single-column primary keys in tables. These are efficient for indexes. They are easy to reference as foreign keys. They provide information about the ordering of rows. For a clustered index, they are easy, because they always go on the last page. The latter is a slight downside compared to GUIDs, because they have slightly more information about each entity. Admittedly, in a simple junction table, they are also redundant.

Note that in MySQL the primary key is automatically the clustered index. Hence, you do not need an additional index on the first key in the primary key. In your example, KEY IX_EntityId (EntityId) is redundant, unless you really care about the performance difference between a B-tree and a hash table.