Understanding of primary key and foreign key in database
In relational database, the data structure has logical structure and physical structure. Physical structure refers to the structure of data files stored on physical media. The logical structure is the relationship, which is a sheet of two-dimensional tables. A column in a table is a field (property) that represents an attribute of an entity. A row in a table is a record.
such as: Student in the table (school number, name, age, Sex), there are 4 fields in the table, representing the 4 attributes of the student entity. A row of data in the table (001, John, Male, 20), a record that represents the student's information John.
In a table, a set of fields that uniquely identifies a record, called the primary key or primary key, the primary key (the primary code), and the primary key contains attributes (fields) called the primary attribute, and the other is a non primary attribute.
To illustrate here, since it is a "field set," The primary key may be a field or multiple fields. For example, the student table above, the school number is the only identification of a student's field, the Number field is the table's primary key. Since the names of the students are unavoidable, it is generally not necessary to use names as the primary key to uniquely identify a student.
Another example illustrates the multiple fields as primary keys. For example, the student chooses the timetable (study number, course number, achievement), in the student chooses the timetable, the main key word is (student number, course number). The reason is obvious, a student can choose multiple courses, a course can be selected by many students. It is not enough to uniquely identify a record if it is simply a school number or a course number.
For the foreign key understanding, here I also give an example. Suppose there are two tables, student form (school number, name, age, sex, professional number), professional information sheet (professional number, professional name, professional memo information). The primary key in the student table is the school number, and the primary key in the Professional information table is the professional number. Non-primary attributes in a student table the professional number happens to be the primary key in the Professional information table. We call this professional number the foreign key to the student table. As such, the non-primary property of a table is the primary property of another table, and the non-primary property is the foreign key.
There are three kinds of database constraints, such as entity integrity constraint, referential integrity constraint and user custom constraint.
1. Entity integrity constraints mean that the primary key cannot be null, and how it uniquely identifies a record if the primary key is empty.
2. Referential integrity constraint, a FOREIGN key constraint, the value of a foreign key must exist in the primary key field it references. For example, the value of the professional number attribute in the student table must be stored in the professional numbering attribute in the professional information table. Think about it and understand that a student (college student) How can belong to a non-existent professional.
3. User-defined integrity constraints, refers to some users set their own constraints, such as whether the field can be empty, field value range (such as: the gender can only be male, female).
