Why is a superkey required when we can uniquely identify a tuple through a primary key?

Question

Superkey and Primary key definition on wikipedia

A superclass is a set of attributes in a table whose values ​​can be used to uniquely identify a tuple.

and

The primary key must consist of characteristics that cannot be duplicated by any other row. The primary key may consist of one attribute or several attributes in combination.

I went through many books and surfed the Internet, but what I found in them is what is the main key and what is the superkey.

But what I want to know is why is a superkey required when we can uniquely identify the tuple via primarykey?

Answer 1

Define what these terms mean first of all:

• A "superkey" is any set of attributes that, when combined, uniquely identifies the rows in the table.
• A minimum of ¹ superkey is called a candidate key or simply a key .
• All keys in one table are logically equivalent, but for historical and practical reasons we select one of them and call it "primary" , and the rest - "alternate" .

So, each primary key is a key, but not every key is a primary. Each key is a super-key, but not every super-key is a key.

The restrictions that physically apply keys in the database are: PRIMARY KEY constraint (for the primary key) and UNIQUE constraint (for the alternate key). These restrictions should not be created on all super-keys, only on keys.

There is nothing unusual in the fact that in one table there are several keys, depending on the nature of your data. For example, a USER table may have a unique USER_ID and a unique USER_NAME. Since both of them must be unique in themselves, you must create ² both keys, although only one of them is strictly required for identification.

---

¹ That is, a superkey that will cease to be unique (and, therefore, will become a superkack) if any of the attributes has been removed from it.

² Ie create a PRIMARY KEY or UNIQUE constraint.

Answer 2

Superclasses are defined for conceptual completeness. You do not need a super click for reference purposes. Link to the primary key will be very convenient.

The concept of superkeys can be useful when you analyze a collection of data in order to discover all the functional dependencies in it.

As soon as you open the key, the next question: regardless of whether it is a superkey. If there is, you pay attention to the candidate key contained in the super-key.

Answer 3

• The word key usually short for a candidate key .
• Superkey means a super-key set (key attributes and some others).
• The irreducible Superkey is called a candidate key . (Irreducible means that if you delete one attribute, this is no longer the key); in general, for a given relation, there is more than one candidate key (in fact, a relational variable).
• One candidate key that the designer prefers (for some reason) is called the primary key .

It was at a logical level, keys are defined for relational variables, the so-called relvars.

In physical implementation:

• Relvar is mapped to a table.
• The primary key to the primary key of the table.
• Other candidate keys (except PC) are mapped to alternative keys (uniquely non-null).

Answer 4

The primary key is a super-key. Having only one such key constraint and only one way to define tuples is not necessarily sufficient.

First, the universality of the relational model is very different from the fact that it does not determine how data can or should be available in the table. The user or application can query the table based on any set of attributes that may be necessary or convenient at the time. There is no obligation to use a “primary” key, which may or may not be relevant for some requests.

Secondly, uniqueness constraints (usually on candidate keys) are a function of data integrity. They ensure that data is not duplicated in key attributes. Such a restriction is often useful for more than one set of attributes, where business rules dictate that things must be unique. The uniqueness of a thing alone obviously does not guarantee the uniqueness of another thing.

Third, the query optimizer can take advantage of all and all keys as a way to optimize access to data by rewriting queries. From the point of view of the optimizer, the more keys with which it should work in the table, the better.

Answer 5

I think superkey is just part of the abstraction of relational algebra - your primary key (most likely) will be the minimum superkey, but you can have other super keys, while you only have one primary key.