Database understanding Super Key, candidate key, primary key, main attribute, foreign key __ Database

Source: Internet
Author: User

First look at the definitions of the various keys:

Super Key: An attribute set that uniquely identifies a tuple in a relationship is called a super key for a relational pattern

candidate Keys (Candidate key): A key that does not contain extra attributes is called a candidate key

primary KEY (primary key): A candidate key program primary key that the user chooses as the tuple identifier

FOREIGN Key (foreign key) if a property set in the relational schema R1 is not a primary key of R1, but is a primary key of another relationship R2, the property set is the foreign key of the relational schema R1.

Combined with the specific explanation of the example:

The following two tables are assumed:

Students (student number, name, gender, ID number, teacher number)

Teacher (teacher number, name, salary)

Super Key:

As defined by the definition of a key, a student's table contains any combination of a school number or an ID number that is a key to this table. such as: (study number), (school number, name), (ID number, gender) and so on.

candidate Key:

The candidate key is a super key, which is the smallest key, meaning that if you remove any of the candidate keys, it is no longer a super key. The candidate keys in the student table are: (school number), (ID number).


The primary key is the candidate key inside of one, is artificial stipulation, for example, in the student table, we usually let "school number" master key, teacher table let "teacher number" key.


The foreign key is simpler, and the foreign key in the student table is the teacher number. Foreign keys are mainly used to describe the relationships of two tables.

Primary properties and Primary keys

Strictly speaking
The main attribute: The primary key column, where a primary key is defined by a column that makes up a
primary key: A property or set of properties that uniquely identifies a tuple, which can consist of multiple columns.
in teaching, most examples are the primary key from a column, so it can be simply said that the main property and the primary key is no different.

First Paradigm (1NF)

The first normal form means that each column of a database table is an indivisible basic data item, and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate attributes. This is the basic concept of the first paradigm.

What do you mean, for example, there is a "phone" field in a table that has a cell number and a landline number so it doesn't fit in the first paradigm. At this point, the phone should be split into "mobile" and "landline" so that it becomes a data table that conforms to the first normal form. Summary: Fields cannot be divided again.

Second Normal form (2NF)
The second normal form is established on the basis of the first paradigm, that is, satisfying the second normal form must first satisfy the first normal form. The second paradigm requires that each instance or row in a database table must be uniquely differentiated. For implementation differentiation, it is often necessary to add a column to the table to store the unique identities of each instance, requiring that the attributes of the entity depend entirely on the primary key. The prerequisite for discussing a table character that does not conform to the second normal form is that the primary key of the table is a combined primary key, and if it is not a combined primary key, then no character does not conform to the second normal form.
Summary: cannot be partially dependent, that is, when a table has a combined primary key, other fields that are not primary keys must be completely dependent on the primary key.

Third Paradigm (3NF)

The third paradigm is to conform to the second normal form, which is that a data table cannot have non-primary key fields in other data tables, that is, if there are fields in other tables, it must be the primary key of that table.

If a table has a B C three fields, A->b b->c. Then C and a are transitive dependencies, which do not conform to the third paradigm. And whether a can determine directly that C is a transitive dependency, only B and C do not depend on any field other than a to conform to the third normal form. Summary: There can be no pass-through dependencies, that is, fields other than primary keys must rely on primary keys and cannot rely on other fields.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.