Does an Empty SQL Table Have a Superkey? Does Every SQL Table Have One

Why can a database table have only one primary key?

The major reason is because that is the definition of the primary key. A table can have multiple unique keys that identify each row, but only one primary key.

In databases such as MySQL, the primary key is also a clustered index. That provides a more direct reason. The data is sorted on the pages according to the clustered index. A table can only have one sort order.

Examination Question regarding Database Management System

What are the attributes called which combine to form a primary key?

Under the relational model:

There's no such special term. The attributes that appear in some CK (candidate key) are called the prime attributes. A PK (primary key) is one CK that you decided to call the PK.

The attributes of a PK are prime, but not all prime attributes need to be attributes of a PK, since there can be CKs other than the PK, or maybe no CK was chosen as PK.

If a relation has only one CK & it is the PK then the PK's attributes are the prime attributes.

But we don't need a special term since we could just say "PK attribute". Just like we don't need "prime" since we could just say "CK attribute".

The answer given is Super key

A superkey is a set of columns whose values are unique. A CK is a superkey that contains no smaller superkey. So a PK is a superkey. But there can be superkeys that are not CKs or PKs--every larger superset of a CK.

PS In SQL, assuming no duplicate rows or duplicate columns names or nulls, so we can take a table to be a relation in the obvious way & apply relational terminology, a UNIQUE declaration or so-called PK declaration declares a superkey.

How do primary keys work in junction tables for a DBMS? How can a composite key be a primary key?

When we use one of those terms we have to be talking about a given table (variable, value or expression). The superkeys, CKs & PKs of a table are not determined by roles its attributes play in other tables. They are determined by what valid values can arise for the table under the given business rules.

Superkey - An attribute or a set of attributes that uniquely identifies a row in a database.

A superkey of a given table can be defined as a set of attributes that "uniquely identifies a row" of the table. (Not database.) Although that quoted phrase is a kind of shorthand that isn't a very clear description if you don't already know what it means.

A superkey of a given table can be defined as a set of attributes whose subrow values can only appear once in the table. Or as a set of attributes that functionally determines every set of attributes in the table.

When a superkey has just one attribute we can sloppily talk about that attribute being a superkey.

Candidate Key - An attribute or set of attributes that uniquely identifies a row in a database.

It's true that every CK (candidate key) of a certain table is a superkey of that table. But you mean that a set of attributes is by definition a superkey when/iff that and some other condition(s) hold. But you don't clearly say that when you write this section.

The difference between the superkey and a candidate key is no subset of a candidate key can itself be a candidate key.

No. A set is a subset of itself so a CK is a subset of itself so a CK always has a subset that is a CK--itself. What you mean is, no proper/smaller subset. Then your statement is true. But also true and more important is that no proper/smaller subset of a CK is a superkey.

You don't actually define "CK" in this paragraph. A CK of a given table can be defined as a superkey of that table that contains no proper/smaller subset that is a superkey of that table.

Primary key - A chosen candidate key that becomes the attribute to uniquely identify a row.

No. The PK (primary key) of a given table is defined as the one CK of that table that you decided to call the PK. (Not attribute.)

Note that CKs & PKs are superkeys. PKs don't matter to relational theory.

To create the relation between both:

AuthorBook(authorID, BookID) -- together authorID and BookID are primary key

What the superkeys & CKs are & so what the PK can be is determined by the FDs (functional dependencies) that hold in the table. But if you are presuming that this is a many to many table then it takes an authorID-bookID pair to uniquely identify a row, so there can only be one CK, {authorID, bookID}. So that is the only possible PK. So {authorID} & {bookID} cannot be superkeys or CKs or PKs.

You can see this by looking at examples & applying the definitions.

authorID bookID
      1      a
      1      b

Here authorID does not uniquely identify a row. So it can't be a superkey. So it can't be a CK. So it can't be a PK.

textbooks I have read seem to define junction tables this way and define primary keys this way

No, they don't.

However they do say that certain sets of attributes & subsets of superkeys, CKs & PKs in the junction table are FKs (foreign keys) in the junction table referencing those other tables where they are CKs (which might be PKs) of/in those other tables.

A FK of a given table can be defined as a certain set of attributes in the table whose subrow values must appear as certain CK subrows in a certain other table.

But since you say this is a junction table, presumably {authorID} is a FK to an author table where its values appear under a CK/PK & {bookID} is a FK to a book table where its values appear under a CK/PK. So FK {authorID} in AuthorBook referencing {authorID} in Author & FK {bookID} in AuthorBook referencing {bookID} in Book.

PS PK & other terms mean something else in SQL. A declared SQL PK can have a smaller SQL UNIQUE declared within it. SQL "uniqueness" itself is defined in terms of SQL NULL. It's reasonable to say that an SQL PK is more reminiscent a relational superkey than it is reminiscent of a relational PK. Similarly a SQL FK is more reminiscent of what we could reasonably call a relational foreign superkey than a relational foreign key.

What are the differences between a superkey and a candidate key?

Candidate key is a super key from which you cannot remove any fields.

For instance, a software release can be identified either by major/minor version, or by the build date (we assume nightly builds).

Storing date in three fields is not a good idea of course, but let's pretend it is for demonstration purposes:

year  month date  major  minor
2008  01    13     0      1
2008  04    23     0      2
2009  11    05     1      0
2010  04    05     1      1

So (year, major, minor) or (year, month, date, major) are super keys (since they are unique) but not candidate keys, since you can remove year or major and the remaining set of columns will still be a super key.

(year, month, date) and (major, minor) are candidate keys, since you cannot remove any of the fields from them without breaking uniqueness.

---

Related Topics