0

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Andrew
  • 13
  • 4
  • 1
    The relational model stipulates that there can **only be ONE** primary key per table. There's no "workaround" for this - it's just the way it is. Not sure what you're trying to achieve - but you must pick **one** candidate key as your primary key, and the others are considered "alternate keys" (but they are **NOT** a second or third "primary key" - that's just not possible) – marc_s Dec 10 '18 at 05:06
  • 1
    A SQL FK must reference a PK or UNIQUE column set. These are more or less relational "superkeys". A superkey that contains no smaller superkey is a CK. Only CKs matter. In SQL PK just means UNIQUE NOT NULL, although some DBMSs use it for other things like default indexing & physical storage. There is no role in relational theory for PKs, it's just a tradition. The CKs other than the PK are called AKs. PDBA has some right & some wrong notions re the relational model. – philipxy Dec 10 '18 at 06:33
  • Possible duplicate of [Can I have multiple primary keys in a single table?](https://stackoverflow.com/q/217945/3404097) – philipxy Dec 10 '18 at 06:47
  • Two PKs are invalid in standard SQL & any DBMS. Your link's code will not compile. The comments say that it won't compile. We do not know why the author uses PRIMARY KEY twice. Maybe the 2nd is a typo for UNIQUE. Maybe it is a typo for pseudo-code ALTERNATE KEY in the constraint named AK. Maybe they use PRIMARY KEY as pseudo-code both times to mean CANDIDATE KEY. (One CK can be PK, others are AKs.) Just use PRIMARY KEY for at most one constraint and UNIQUE (NOT NULL) for the remaining. And post a comment and ask them why there are 2 PK declarations. PS That post is muddled re what a PK is. – philipxy Dec 10 '18 at 09:54
  • I'd like to quote Seth Godin; "If you’re not able (or committed enough) to do the reading before you give your opinion, please have the guts to point that out." https://seths.blog/2018/12/i-didnt-do-the-reading/ – Andrew Dec 13 '18 at 21:42

2 Answers2

4

In Microsoft SQL Server there is little or no difference between a key created as PRIMARY KEY and a key created using a UNIQUE constraint (on non-nullable columns of course). You can define upto 1000 UNIQUE constraints per table. There are one or two minor differences in syntax but there's no particularly strong reason to use a PRIMARY KEY constraint rather than UNIQUE constraint.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

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.

Drooten
  • 38
  • 6
  • I voted this answer simply because it 'was the most helpful in finding my solution'. Thanks for the feedback, Drooten. – Andrew Dec 20 '18 at 23:35
  • There's a lot of meta commentary in this response. Think maybe you can pare it down a bit? This isn't a discussion forum; *let your answers be answers,* not speeches or chats. – Robert Harvey Dec 21 '18 at 23:33
  • While StackOverflow is intended for quick, perhaps impulsive, responses to rather simplistic questions, database questions / answers tend to be long given that they involve actual well thought out answers. Database / Relational Modelling is supposed to be a science after all. Thanks. – Andrew Jan 21 '19 at 04:35