Talking about the foreign key (FK)

A Definition of foreign keys:

A foreign key (FK) is one or more columns that are used to establish and strengthen a link between two table data. You can create a link between two tables by adding one or more columns that hold the primary key values in the table to another table. This column becomes the foreign key of the second table.

The main purpose of the FOREIGN key constraint is to control the data stored in the Foreign key table, but it can also control the modification of the data in the primary key table. For example, if you delete a publisher in the publishers table, and the publisher's ID is used to record the book's information in the titles table, the integrity of the association between the two tables is broken, and the publisher's book in the titles table is associated with the publishers The data in the table is not linked and becomes orphaned. The FOREIGN KEY constraint prevents the occurrence of this condition. If the changes in the data in the primary key table invalidate the link to the data in the Foreign key table, this change is not possible, thus ensuring referential integrity. If you attempt to delete a row in a primary key table or change a primary key value that is related to the FOREIGN key constraint value of another table, the operation is not implemented. To successfully change or delete a row for a foreign key constraint, you can first delete the foreign key data in the external key table or change the foreign key data, and then link the foreign key to the different primary key data.

Foreign keys are used to control the data integrity of the data in a database when you manipulate the data of a table and the data of one or more tables that he is associated with can change at the same time, which is the role of the foreign key.

Two The difference between a foreign key and a primary key:

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.

(a) Primary key: The relational database relies on the primary key---it is the cornerstone of the database physical schema. 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 purposes, here are some principles to follow when designing a primary key for a 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 first method above becomes part of the table primary key, It is also possible for the table to become the other primary table from the table, and its primary key may become part of the other from the table primary key, so pass on, the more the back of 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.

(b) Foreign key: is a database level integrity constraints, is the database theory of the "referential integrity" of the implementation of the database.

Foreign key attributes of course can be removed, if you do not want to use this constraint, the programming of course will not have any effect, but the corresponding input data when the data entered is not "referential integrity" check.

For example, there are two of tables

A (b): A is the primary key, and B is a foreign key (from b.b)

B (b,c,d): B primary Key

If I remove the foreign key attribute of field B, it has no effect on programming.

As above, the B in a is either empty or the value exists in B, and when there is a foreign key, the database automatically checks to see if B is present in B.

1, the external expression is the referential integrity: this is the data inherent, and program-independent. Therefore, it should be given to the DBMS.

2, the use of external construction, simple and intuitive, can be directly reflected in the data model, whether it is the design, maintenance, etc. back has great benefits, especially for the analysis of the benefits of the existing database is very obvious-not long ago I analyzed an enterprise existing database, the referential integrity constraints there are some foreign key description, Some are implemented with triggers and feel obvious. Of course, the documentation may be, but it may not be all, but the foreign key is very obvious and intuitive.

3. Since we can do this work with triggers or programs (referring to referential integrity constraints), the DBMS has provided the means for us to do it ourselves. And we should say that no RDBMS is doing well. In fact, the early RDBMS did not have foreign keys, and now there are, I think the database vendors to add this function is justified. From this point of view, the foreign key is more convenient.

4, about the convenience, according to the situation of my belt project, the programmer does have the reflection, mainly in debugging the input data trouble: if the data can violate referential integrity, then the referential integrity itself is not the reputation of the business conflict, this should not be used to trigger the futures program to achieve; otherwise, the data is wrong, You should not enter the database at all. Also, this should be a part of the test system: block illegal data. In fact, the foreground program should handle this submission failure. Data is an enterprise rather than a program, and the storage program should be separated from the data as far as possible, and vice versa.

Finally, some principles of key building:

1. Create a foreign key for the associated field.

2. All keys must be unique.

3. Avoid the use of composite keys.

4. The foreign key is always associated with a unique key field.

Three The role of foreign keys:

Maintain data consistency, integrity, the primary purpose is to control the data stored in the Foreign key table. Causes two tables to be associated, and the foreign key can only refer to the values of the columns in the outer table.

For example:

A B two tables

Account number in Table A, customer name

Each customer's order is stored in table B

After having the foreign key

You can only delete customer x in table A when you are sure there are no orders for customer x in table B.

The precondition for establishing a foreign key is that the column of this table must be the same as the foreign key type (the foreign key must be the outer primary key).

Specify PRIMARY KEY keyword: foreign key (column name)

Referencing foreign key keywords: references < foreign key table name > (foreign key column name)

Event Trigger limit: On delete and on UPDATE, set parameter cascade (following foreign key changes), restrict (Restrict foreign key changes in appearance), SET null (null value), set default, [Default]no action]

For example:

Outtable table primary key ID type int

To create a table that contains a foreign key:

CREATE TABLE Temp (

ID int,

Name Char (20),

FOREIGN key (ID) references outtable (ID) on the delete cascade on Updatecascade);

Description: Set the ID column to foreign key reference the ID column of the outer outtable when the value of the foreign key is removed from the corresponding column in this table except when the value of the foreign key changes the corresponding column value in this table.