Microsoft T-SQL does not support more than one Primary Key; Suggested Workarounds?

I was reading a brilliant response provided by PerformanceDBA to this SQL Question.

In PerformanceDBA 'Full Example', tables 'user' and 'sport' show two PRIMARY KEY s per table. Note; should you look carefully at PerformanceDBA response, you will notice Primary Key One consists of one single field, while Primary Key Two consists of three fields; a composite Key.

Given Microsoft T-SQL Server does not support more than one Primary Key per table (I was unaware that SQL ANSI did either), how would we achieve the concept presented by PerformanceDBA as a workable Microsoft T-SQL solution (ie, following Microsoft T-SQL syntax)?

Is there a chance the information provided by PerformanceDBA simply includes a typo; an error which he overlooked?

My initial thoughts are (table definition from PerformanceDBA answer to SQL Question with minor modification to suit T-SQL):

    CREATE TABLE [User] (              -- Typical Identifying Table
       [user_name]  CHAR(16) NOT NULL, -- Short PK
       name_first CHAR(30) NOT NULL,   -- Alt Key.1
       name_last  CHAR(30) NOT NULL,   -- Alt Key.2
       birth_date DATE     NOT NULL ,  -- Alt Key.3

        --Create a unique CONSTRAINT and assign a Foreign Key (
       CONSTRAINT User_PK  PRIMARY KEY ( [user_name] ),

       -- Will this 'do'?
       CONSTRAINT User_AK  UNIQUE  ( name_last, name_first, birth_date ),

       CONSTRAINT user_FK -- unique person identification
          FOREIGN KEY (name_last, name_first, birth_date) 
          REFERENCES [Person] ( name_last, name_first, birth_date) 

     )

Thank you for your time.

It's easy to tell from the way you write this question that you actually know what you are talking about re: ANSI / SQL / Microsoft T-SQL, Primary Keys vs Unique index.

Your question is clear and you have provided not only a strong background to your question but have also provided one succinct solution; a task that many do not attempt as part of the initial question.

I have read PerformanceDBA's response and your question remains; Which index was meant to be the Primary Key and which one was meant to be the (UNIQUE) index (and should it be a UNIQUE index)?

I suspect in this scenario that user_name is the desired Primary Key, given not only that it needs to be unique, but in many systems an individual log on MUST be unique. In this scenario first name, middle name and DOB become the Foreign Key (UNIQUE INDEX).

There may actually be a scenario where two people exist with the same first name, middle name and DOB (thus you may not want a UNIQUE Index on these three fields). Given you only provided basic table designs, I'll surmise that a lot more work would need to go into the design requirements to correctly sort this issue out.

This may not be as detailed as the responses provided by PerformaceDBA, but I sincerely hope it is the kind of interaction & feedback you were hoping to receive.