Composite primary key comprising two foreign keys referencing same table: SQL Server vs. MySQL

Question

I've read over a number of posts regarding DB table design for a common one-to-many / users-to-friends scenario. One post included the following:

USERS

* user_id (primary key)
* username

FRIENDS

* user_id (primary key, foreign key to USERS(user_id))
* friend_id (primary key, foreign key to USERS(user_id))

> This will stop duplicates (IE: 1, 2) from happening, but won't stop reversals because (2, 1) is valid. You'd need a trigger to enforce that there's only one instance of the relationship...

The bold portion motivated me to post my question: is there a difference between how SQL Server and MySQL handle these types of composite keys? Do both require this trigger that the poster mentions, in order to ensure uniqueness?

I ask, because up until this point I've been using a similar table structure in SQL Server, without any such triggers. Have I just luckily not run into this data duplication snake that's lurking in the grass?

Answer 1

Yes, all DBMS will treat this the same. The reason is that the DBMS assumes that the column has meaning. I.e., the tuple is not comprised of meaningless numbers. Each attribute has meaning. user_id is assumed to have different meaning than friend_id. Thus, it is incumbent upon the designer to build a rule that claims that 1,2 is equivalent to 2,1.