mysql中alternatekey定义,Differences between key, superkey, minimal superkey, candidate key and primary ...

I'm new to MySQL, and I'm really confused about the different terms that I've encountered. I tried googling the answer but the results are really confusing and when I try and understand it just seems like they are the same thing.

What exactly are the differences among key, superkey, minimal superkey, candidate key and primary key?

bHaRaTh

Here I copy paste some of the information that I have collected

Key

A key is a single or combination of multiple fields. Its purpose is to access or retrieve data rows from table according to the requirement. The keys are defined in tables to access or sequence the stored data quickly and smoothly. They are also used to create links between different tables.

Types of Keys

Primary Key

The attribute or combination of attributes that uniquely identifies a row or record in a relation is known as primary key.

Secondary key

A field or combination of fields that is basis for retrieval is known as secondary key. Secondary key is a non-unique field. One secondary key value may refer to many records.

Candidate Key or Alternate key

A relation can have only one primary key. It may contain many fields or combination of fields that can be used as primary key. One field or combination of fields is used as primary key. The fields or combination of fields that are not used as primary key are known as candidate key or alternate key.

Composite key or concatenate key

A primary key that consists of two or more attributes is known as composite key.

Sort Or control key

A field or combination of fields that is used to physically sequence the stored data called sort key. It is also known s control key.

A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

Example for super key:

Imagine a table with the fields , , and . This table has many possible superkeys. Three of these are , and . Of those listed, only is a candidate key, as the others contain information not necessary to uniquely identify records.

Foreign Key

A foreign key is an attribute or combination of attribute in a relation whose value match a primary key in another relation. The table in which foreign key is created is called as dependent table. The table to which foreign key is refers is known as parent table.

I have always found it difficult to remember all the keys; so I keep the below notes handy, hope they help someone! Let me know if it can be improved.

Key: An attribute or combination of attributes that uniquely identify an entity/record in a relational table.

PK: A single key that is unique and not-null. It is one of the

candidate keys.

Foreign Key: FK is a key in one table (child) that uniquely identifies a row of another table (parent). A FK is not-unique in the child table. It is a candidate key in the parent table. Referential integrity is maintained as the value in FK is present as a value in PK in parent table else it is NULL.

Unique Key: A unique key that may or may not be NULL

Natural key: PK in OLTP. It may be a PK in OLAP.

Surrogate Key: It is the Surrogate PK in OLAP acting as the

substitute of the PK in OLTP. Artificial key generated internally in

OLAP.

Composite Key: PK made up of multiple attributes

SuperKey: A key that can be uniquely used to identify a database record, that may

contain extra attributes that are not necessary to uniquely identify

records.

Candidate Key: A candidate key can be uniquely used to

identify a database record without any extraneous data. They are Not

Null and unique. It is a minimal super-key.

Alternate Key: A candidate key that is not the primary key is called an alternate key.

Candidate Key/s with Extraneous data: Consider that

can be used to identify a record in the Employee table but candidate

key alone is sufficient for this task. So

becomes the extraneous data.Note that the PK, Foreign Key, Unique Key, Natural key, Surrogate Key,

Composite Key are defined as Database objects; where the Natural key

is a PK in the OLTP and could be a PK in the target OLAP. For the rest

of the keys, it's up to the DB designer/architect to decide whether

unique/not-null/referential integrity constraints need to enforced or

not.

Below I have tried to use set theory to simplify the representation of the membership of the keys w.r.t. each other.

key = { All of the below keys }

PK = { PK }

Foreign Key = { Key with Not Null constraint }

Unique Key = { {Candidate Key/s}, {attributes containing NULL} }

Natural key = { PK }

Surrogate Key = { PK }

Composite Key = { PK }

Super Key = { {Candidate Key/s}, {Candidate Key/s with Extraneous data} }

Candidate Key = { PK, {Alternate Key/s} }

Alternate Key = { {Candidate Keys} - PK }

Candidate Key/s with Extraneous data = { }

I have summarized it below:

Primary key is a subset of super key. Which is uniquely define and other field are depend on it. In a table their can be just one primary key and rest sub set are candidate key or alternate keys.

Superkey - An attribute or set of attributes that uniquely defines a tuple within a relation. However, a superkey may contain additional attributes that are not necessary for unique identification.

Candidate key - A superkey such that no proper subset is a superkey within the relation. So, basically has two properties: Each candidate key uniquely identifies tuple in the relation ; & no proper subset of the composite key has the uniqueness property.

Composite key - When a candidate key consists of more than one attribute.

Primary key - The candidate key chosen to identify tuples uniquely within the relation.

Alternate key - Candidate key that is not a primary key.

Foreign key - An attribute or set of attributes within a relation that matches the candidate key of some relation.

Largely based on the accepted answer, but with a few tweaks to fit better the definitions taught in some courses:

Key: A set of $\ge1$ columns.

Superkey: A key that $\supseteq$ a candidate key.

Therefore, a superkey must contain $>1$ columns.

Minimal Super key $\equiv$ Candidate Key: A key that can uniquely identify each row in a table.

Primary Key: The chosen Candidate Key for doing that.

Secondary key / Alternate key: A Candidate Key not chosen for doing that.

Search Key: A key used for locating records.

Composite key or concatenate key: A key with $>1$ columns.

Usually implies "composite primary key", although "composite alternate key" is also a thing.

Sort or control key: A key used to physically sequence the stored data.

Foreign Key A key in one table that matches the Primary Key of another table.

The table in which foreign key resides is called as dependent table.

The table to which foreign key refers is known as parent table.

SUPER KEY:

Attribute or set of attributes used to uniquely identify tuples in the database.

CANDIDATE KEY:

Minimal super key is the candidate key

Can be one or many

Potential primary keys

not null

attribute or set of attributes to uniquely identify records in DB

PRIMARY KEY:

one of the candidate key which is used to identify records in DB uniquely

not null

Super Key : Super key is a set of one or more attributes whose values identify tuple in the relation uniquely.

Candidate Key : Candidate key can be defined as a minimal subset of super key. In some cases , candidate key can not alone since there is alone one attribute is the minimal subset. Example,

Employee(id, ssn, name, addrress)

Here Candidate key is (id, ssn) because we can easily identify the tuple using either id or ssn . Althrough, minimal subset of super key is either id or ssn. but both of them can be considered as candidate key.

Primary Key : Primary key is a one of the candidate key.

Example :

Student(Id, Name, Dept, Result)

Here

Super Key : {Id, Id+Name, Id+Name+Dept} because super key is set of attributes .

Candidate Key : Id because Id alone is the minimal subset of super

key.

Primary Key : Id because Id is one of the candidate key

Candidate Key: The candidate key can be defined as minimal set of attribute which can uniquely

identify a tuple is known as candidate key. For Example, STUD_NO in below

STUDENT relation.

The value of Candidate Key is unique and non-null for every tuple.

There can be more than one candidate key in a relation. For Example, STUD_NO as well as STUD_PHONE both are candidate keys for

relation STUDENT.

The candidate key can be simple (having only one attribute) or composite as well. For Example, {STUD_NO, COURSE_NO} is a composite

candidate key for relation STUDENT_COURSE.

Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO,

STUD_NAME) etc. Adding zero or more attributes to candidate key

generates super key. A candidate key is a super key but vice versa is

not true. Primary Key: There can be more than one candidate key in a

relation out of which one can be chosen as primary key. For Example,

STUD_NO as well as STUD_PHONE both are candidate keys for relation

STUDENT but STUD_NO can be chosen as primary key (only one out of

many candidate keys).

Alternate Key: The candidate key other than primary key is called as alternate key. For Example, STUD_NO as well as STUD_PHONE both are

candidate keys for relation STUDENT but STUD_PHONE will be alternate

key (only one out of many candidate keys).

Foreign Key: If an attribute can only take the values which are

present as values of some other attribute, it will be foreign key to

the attribute to which it refers. The relation which is being

referenced is called referenced relation and corresponding attribute

is called referenced attribute and the relation which refers to

referenced relation is called referencing relation and corresponding

attribute is called referencing attribute. Referenced attribute of

referencing attribute should be primary key. For Example, STUD_NO in

STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.

Superkey

A superkey is a combination of attributes that can be uniquely used to identify a

database record. A table might have many superkeys.Candidate keys are a special subset

of superkeys that do not have any extraneous information in them.

Examples: Imagine a table with the fields , , and .

This table has many possible superkeys. Three of these are ,

and .Of those listed, only is a **candidate key**, as the others

contain information not necessary to uniquely identify records.

来源：https://stackoverflow.com/questions/6951052/differences-between-key-superkey-minimal-superkey-candidate-key-and-primary-k