First, what is the primary key, foreign key
There are several properties in a record in a relational database if one of the attribute groups (note that
Group) can uniquely identify a record the attribute group can be a primary key
Like what
Student Table (School number name sex Class)
Each student's school number is the only school number is a primary key
Curriculum (course number, course name, credits)
Where the course number is unique, the course number is a primary key
Score Table (School number, course number, score)
A single attribute in the score table cannot uniquely identify a combination of a record number and a course number to
To uniquely identify a record so the attribute group for the study number and course number is a primary key
The school number in the score table is not the primary key of the score table, but it corresponds to the student number in the students table and
The student's form is the primary key of the student table, and the student number in the table is the foreign key of the students ' table.
The course number in the results table is the foreign key of the curriculum.
Primary keys and foreign keys are defined primarily to maintain the integrity of the relational database.
Summarize
1. A primary key is a unique identifier that can determine a record. For example, a record includes an identity plus, last name
Name, age.
The ID number is the only one who can determine who you are. Others may have duplicates, so the ID number
is the primary key.
2. A foreign key is used to associate with another table. is a field that determines that another table record is used to hold
Consistency of data.
For example, a table in a field is the primary key of table B, then he can be a foreign key to the table.
Ii. differences between primary key, foreign key, and index
What is the difference between a primary key, a foreign key, and an index?
Primary key FOREIGN Key index
Definition: Uniquely identifies a record, cannot have duplicate, does not allow null table for foreign key is another table
The primary key, the foreign key can have duplicates, can be null, the field does not have duplicate values, but can have a null value
To ensure that data integrity is used to connect with other tables by increasing the order of queries
Speed
The number of primary keys can have only one single table may have multiple foreign keys a table can have multiple unique indexes
What is the difference between a clustered index and a nonclustered index?
A clustered index must be a unique index. But a unique index is not necessarily a clustered index.
Clustered index: Storing data directly in the index page instead of the clustered index in the index page
Index these indexes to point to data on specialized data pages.
Design principle of primary key and foreign key in database
Primary keys and foreign keys are binders that organize multiple tables into an effective relational database. Primary KEY and outer
The design of keys has a decisive impact on the performance and usability of the physical database.
The database schema must be converted from a theoretical logical design to an actual physical design. While the primary key and
The structure of the foreign key is the crux of the design process. Once the designed database is used in the production ring
It is difficult to modify these keys, so designing the primary and foreign keys at the development stage is very
Necessary and worthwhile.
Relationship structure of the primary foreign key:
1, one-to-one, without reference to the primary foreign key, put them in a table can
For example: A student can have only one card number, then the student and the card number in a table can be
2, one-to-many, referring to the primary foreign key, ' a ' equivalent to the primary key, ' more ' is the foreign key referencing the primary key.
For example: A class can have more than one student, and a student can only belong to a class, this is a one-to-many relationship;
3, many-to-many relationship, need to create a table, the table requires two fields, respectively, is a reference, two many-to-many field primary key foreign key.
For example:
Student's form (school number, name, ...). ) PRIMARY key: study number
Class Schedule (course number, course name, ...). ) Primary key: Course number
A student can choose multiple courses, a class can be selected by multiple students, that is, many-to-many relationships,
So the score table is the body of the relationship between them,
That is to cite the score table (school number, course number, score). Here the student number and the course number are the foreign keys of the students ' table and the curriculum number and the school number.
In our present phase, we are mainly using a one-to-many relationship between the main foreign keys
Primary key primary key, which uniquely identifies an entity. is to ensure the entity integrity of the database, ensure the correctness and rationality of data, the value of non-null unique.
Foreign key foreign, which is used to connect tables to tables. To ensure the referential integrity of the database, the foreign key must be taken from the Reference table Reference column value, either empty or blank.
Database primary key and foreign key
