In the second example you show, the xid, yid columns are indexed but nothing prevents your application from entering the same pair of xid, yid on multiple rows of the ABC table:

INSERT INTO ABC (xid, yid) VALUES (123, 456), (123, 456); -- NO ERROR

You can get unintentional duplicates this way, and it can cause strange effects when you do joins and counts. Also if you need to update a row to change the association between a given xid and its yid, you could update one row and not the other(s).

You should at least declare the key over (xid, yid) as a UNIQUE KEY to prevent duplicates.

The first example you show uses a compound primary key (some people say composite primary key). SQL supports multi-column indexes, and multi-column constraints. There s no downside to doing this, except that if you want to run a query to pick one row out, you need to use two columns instead of one in the condition that identifies the row.

DELETE FROM ABC WHERE xid = 123 AND yid = 456;

Likewise if another table contains a foreign key to reference the ABC table, it would have to have both columns.

Enough programmers find using two columns to be so burdensome and confusing that they d rather add a single-column surrogate key.

Insisting on a superfluous surrogate key when none is needed is something I consider to be an SQL Antipattern.

Re your updated question above: Are you aware that a compound index only helps when your search includes the left-most columns in the index? This is true of any composite index in any brand of RDBMS. Example:

CREATE TABLE myC (
  somethingid INT,
  userid      INT,
  PRIMARY KEY (somethingid, userid)
);

SELECT * FROM myC WHERE userid = 12345;

This query cannot use the primary key index.

The classic example to explain compound index usage is the telephone book analogy: If I ask you to search for everyone whose last name is "Thomas" you can use the fact that the phone book is ordered by last name to help make your search quick. But if I ask you to search for everyone whose first name is "Thomas," you have to search every page. The phone book is like a compound index on (last_name, first_name). So if your search doesn t include last_name, you must resort to a brute-force search.

You can also create an extra index just for the other column, so you can do a search using that as the criterion. You don t need an extra single-column index for the first column. The compound index is adequate.

CREATE TABLE myC (
  somethingid INT,
  userid      INT,
  PRIMARY KEY (somethingid, userid),
  KEY (userid)
);

Usually if that column is declared as a foreign key, the RDBMS should create an index automatically. However, in some versions of some RDBMS products, you have to create an index on a foreign key column yourself, as a separate action.

Adding the id column is only worth doing if you are exposing the contents to the user. Either way, it s a typical many-to-many join table.

To change the primary key to a single column means adding a unique key constraint onto the two foreign key columns - there s no need because you get the uniqueness from defining the columns as the primary key, even if it is a composite key. A composite key is a combination of 2+ columns.

If you aren t selecting by that id column, there s no need for it.

What you re describing is a composite primary key, which is acceptable and a sensible pattern. A lot of applications use an artificial primary key (usually an int or guid) when there is a perfectly acceptable candidate key already. This adds work for the database, but in some cases makes the application easier to write.

There is no real reason to add an extra primary key when there is already a candidate. I d lean towards not doing so.

You also gain performance in some cases by not adding an unnecessary auto-generated primary key column, and it reduces the space usage of your database (albeit only slightly)

There s one other cool thing about primary keys which is that the data is actually ordered by the primary key on the disk. So there is a slight difference between even a unique index and a primary key when it comes to performance. It s probably not going to be much of a performance difference though, just how much time it takes to actually get the data off of the disk.