Primary key and foreign key

Primary key: There are several attributes in the record, and if one of the attribute groups (note is a group) can uniquely identify a record, the attribute group can become a primary key;

PRIMARY key   //primary key

Your foreign key is the primary key of the other table so join in the  B-table secondary number is the primary key  and just the number is a table in a field is the number B is the foreign key in a table

Alter Table Add constraint Foreign Key REFERENCES Appearance table name  //Add foreign key

[CONSTRAINT Symbol] FOREIGN KEY [ID](Index_col_name, ...) REFERENCEStbl_name (index_col_name, ...) [On DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]    [On UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

Format of foreign keys
CONSTRAINT foreign Key Name FOREIGN key (field) REFERENCES parent table (field)


ALTER table name DROP FOREIGN key foreign key alias;

Like what:
                      Score Table (school number, score, name, class, serial number)
                      Student Table (School number, name, gender, Class)

Each student's school number is unique, the study number is a primary key

User table (user name, password, logon level)

Where the user name is unique and the user name is a primary key

on-Board recording table (card number, school number, name, serial number)

A single attribute in the record table cannot uniquely identify a record, and the combination of the number and name can uniquely identify a record, so the attribute group for the number and name is a primary key

The serial number in the record table is not the primary key of the score table, but it corresponds with the student's number, and the student's table is the primary key of the student table, then the student number in the score table is the foreign key of the students ' table.

Defining primary and foreign keys is primarily to maintain the integrity of the relational database, summarizing:

A primary key is a unique identifier that determines a record, for example, a record that includes a social security number, name, and age. The ID number is the only person who can identify you, others may have duplicates, so the ID number is the primary key.

A foreign key is used to associate with another table. is a field that determines the record of another table, which is used to maintain data consistency. For example, a field in a table is the primary key of table B, so he can be a foreign key to table A.

Primary key, index, foreign key difference

What is the difference between a primary key, a foreign key, and an index?

Definition: Uniquely identifies a record, cannot be duplicated, does not allow the foreign key to be empty table is the primary key of another table, foreign key can have duplicate, can be null value
The field does not have duplicate values, but it can have a null value: to ensure that data integrity is used to connect with other tables, the number of speeds to sort queries is increased: The primary key can only have one
A table can 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 indexes, where the data is stored directly in the index page, and the non-clustered index is indexed on the index page, which points to the data of the dedicated data page.

Primary keys and foreign keys are binders that organize multiple tables into an effective relational database. The design of primary keys and foreign keys has a decisive impact on the performance and availability of the physical database. The database schema must be converted from a theoretical logical design to an actual physical design. The structure of the primary key and the foreign key is the crux of the design process. Once the designed database is used in production environments, it is difficult to modify these keys, so it is necessary and worthwhile to design the primary and foreign keys in the development phase.

Primary key:

The relational database relies on the primary key---it is the cornerstone of the database physical pattern. There are only two uses of the primary key at the physical level:

1. Uniquely identify a row.

2. As an object that can be effectively referenced by a foreign key.

Based on these two uses, here are some of the principles that I followed when I designed the primary key for the physical plane:

1. The primary key should not be meaningful to the user. If a user sees data in a connection table that represents a many-to-many relationship and complains that it is useless, it proves that its primary key is well designed.

2. The primary key should be single-column to improve the efficiency of the connection and filtering operations.

Note: People who use composite keys often have two reasons to justify themselves, both of which are wrong. One is that the primary key should have practical significance, however, making the primary key meaningful is simply a convenience to artificially destroy the database. The second is that using this method can use two foreign keys as the primary key in the join table that describes many-to-many relationships, and I also oppose this practice because the composite primary key often leads to bad foreign keys, that is, when the join table becomes another main table from the table, and according to the secondary method above becomes part of the table's primary key, However, it is possible for the table to become the other main table from the table, and its primary key may become a part of the primary key from the table, so pass on, the farther back from the table, its primary key will contain more columns.

3.
Never update the primary key. In fact, there is no reason to update a primary key because it has no other purpose than to uniquely identify a row. If the primary key needs to be updated, the principle that the primary key should not be meaningful to the user is violated.

Note: This principle does not apply to data that is often required for data consolidation when data conversion or multi-database merging.

4. The primary key should not contain dynamically changing data such as timestamps, creation time columns, modified time columns, and so on.

5.
The primary key should be automatically generated by the computer. If a person is to intervene in the creation of a primary key, it will have a meaning other than a single row of identifiers. Once this boundary is crossed, it is possible to assume the motivation to modify the primary key, so that the key means used by the system to link record lines and manage record lines will fall into the hands of those who do not understand the design of the database.

Primary key and foreign key