What is the difference between a primary key and a superkey in a DBMS?
Can a primary key and a superkey both have multiple columns?
Is a primary key a subset of a superkey or vice versa?
-
1One thing that is glossed over a lot is that the tuple (row) itself is a superkey. Since the intent is to be able to uniquely identify the row, i.e. to be able to uniquely identify the tuple containing a particular combination of values, one way to do that is to already have all the values available. Hence the tuple is a superkey for itself, since once we know the values in the tuple, then clearly we know how to find the tuple having those values. It seems silly at first but it establishes an upper bound on what can be a superkey for a tuple -- the tuple itself.– DaveCommented Feb 13, 2017 at 2:49
-
@Dave The set of all column names of a relation/table (and hence each of its tuples/rows) is a superkey of it. Not "the tuple (row) itself".– philipxyCommented Feb 14, 2018 at 23:20
-
@philipxy you are right and I misspoke -- I stand corrected. Thanks for adding the clarification.– DaveCommented Mar 3, 2018 at 17:07
-
Is this about the relational model or SQL? They use terms differently.– philipxyCommented Jan 18, 2021 at 23:23
Add a comment
|
3 Answers
A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row.
A Primary Key is a minimal Candidate Key, which is to say all constituent columns are strictly required in order to ensure uniqueness.
As a database developer/designer of 30 years experience, I had never even heard the term Super Key until I saw this question, and looked it up. The concept of Super Key seems more germane to the topic of performance and Physical Schema design as it directly maps to the concept of a unique nonclustered index with additional columns for improved query covering.
-
5This is quite inaccurate. You are using superkey, CK & PK in place of proper superkey, superkey & CK. A superkey is a UNIQUE set. (A CK is a "minimal" superkey. There is no notion of "minimal CK". Every CK is a superkey. So a superkey does not have to have more columns than a CK. A PK is a CK one calls the PK. If it had to be a "minimal CK" then "minimal" would have to mean "arbitrary".) SQL PK & UNIQUE NOT NULL declare superkeys. (An SQL PK might or might not be a PK.) Superkeys are critical to the definition of CK, which is critical to design including normalization. See my answer.– philipxyCommented Jul 25, 2017 at 9:10
-
1Additionally, the understanding of SK are needed to use the Boyce-Codd NF– AdvenaCommented Oct 3, 2019 at 10:58
Super Keys: Super Key stands for superset of a key. A Super Key is a set of one or more attributes that are taken collectively and can identify all other attributes uniquely.
For example, consider the table:
Book (BookId, BookName, Author)
So in this table we can have
- (BookId)
- (BookId, BookName)
- (BookId, BookName, Author)
- (BookId, Author)
- (BookName, Author)
As our Super Key. Each Super Key is able to uniquely identify each tuple (record).
Candidate Keys: Candidate keys are a Super Key which are not having any redundant attributes. In other words candidate keys are minimal Super Keys. For example, in the above illustration
- (BookId)
- (BookName,Author)
These two keys can be candidate keys, as remaining keys are having redundant attributes.
Means in Super Key (BookId, BookName) record can be uniquely identify by just BookId and therefore BookName is redundant attribute.
Primary Key: It is a candidate key that is chosen by the database designer to identify entities with in an entity set. OR A key which is used to uniquely identify each record is known as primary key.
From above Candidate keys any one can be the primary key. And the another one which is not chosen as primary key will be know as Alternate key
From this stackoverflow.com answer of mine:
A candidate key is a set of columns that uniquely identifies rows and that contains no smaller ("proper") subset of columns that uniquely identifies rows. A superkey is a set of columns that uniquely identifies rows. So a candidate key is a superkey that contains no smaller superkey. In SQL you can't declare an empty candidate key. Also, UNIQUE NOT NULL and PRIMARY KEY (which in terms of constraints just means UNIQUE NOT NULL) declare superkeys, not keys per se. If such a declaration's column set doesn't contain a smaller column set declared as a superkey then the superkey it's declaring is a candidate key.
From this stackoverflow.com answer of mine re a given table:
For sets of columns X and Y we can write X -> Y. We say that X is the determinant set and Y is the determined set of/in functional dependency (FD) X -> Y.
We say X functionally determines Y and Y is functionally determined by X. We say X is the determinant of X -> Y. In {C} -> Y we say C functionally determines Y. In X -> {C} we say X functionally determines C. When X is a superset of Y we say X -> Y is trivial. We say X -> Y holds in table T when each subrow of values for X always/only appears with the same subrow of values for Y. Or we say X -> Y is a FD of/in T. When X is a determinant of some FD in table T we say X is a determinant of/in T.
A superkey of a table T is a set of columns that functionally determines every attribute. A candidate key (CK) is a superkey that contains no smaller superkey. We can pick one CK as primary key (PK) and then call the other CKs alternate keys. A column is prime when it is in some CK.
(As I commented there, "The four boldface sentences for FD, holds, superkey and CK would have sufficed.")
(A table with an empty CK is constrained to contain at most one row. A column set determined by the empty set is constrained to have the same subrow value in every row.)
Re relational model terms vs what they might or might not mean in SQL:
Does an empty SQL table have a superkey? Does every SQL table?