[Reprint] The use of database foreign keys

[Reprint] The use of database foreign keys

The role of foreign keys:

Maintain data consistency, integrity, the primary purpose is to control the data stored in the Foreign key table. Make two tables associative, and foreign keys can only refer to the values of columns in the outside!

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 UPDATE cascade);

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.

child table, definition of parent table: A table with a foreign key is a child table. The table that the primary key is referenced by another table is the parent table.
In other words: Because the identity of the parent table is referenced by many records in the child table, it is called the parent table.
The table that has a foreign key relationship and can delete data casually, without affecting the data of other tables, is called a child table.

The use of the time who made the foreign key, mainly from the following two points considered:
1/, delete is how to affect each other, delete the record constrained is the parent table, the unconstrained is the child table;
2/, the record must exist first is the parent table;

Two uses:
1/, the most common one: reduce duplication of data. Table A has foreign keys, and table B's data is basically not allowed to be deleted. At this point, you choose to enforce the INSERT and UPDATE relationships .
2/, Second, is to add a subordinate table. If Table A deletes a record, table B also deletes a record associated with it, and in the foreign key relationship, table A's primary key is the foreign key of table B. This relationship, in fact, table B is a subordinate table of table A (that is, table A is the parent table), when you choose to enforce the insert and UPDATE relationship , if you insert data into table B, the corresponding record must already exist in table A. When you select cascade to delete related fields , deleting a record in table A deletes a record in table B.

Today, a friend asked me, "What is the role of foreign key?"

When a friend asked me what the role of foreign keys, I also leng a bit, is usually in so use, has not really summed up, the role of the external key. Below, I summarize the role of the foreign key: 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 data in a database. is when you manipulate the data on a table. The data of one or more tables associated with him can change at the same time. That's what the foreign key does.

[Fine] talk about 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 data in a database.

is when you manipulate the data on a table.

The data of one or more tables associated with him can change at the same time.

That's what the foreign key does.

Foreign key is an integrity constraint at the database level, which is the database realization method of "referential integrity" in the database theory book.

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 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 never get into the database! 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.

What is the role of foreign keys?

Foreign key is an integrity constraint at the database level, which is the database realization method of "referential integrity" in the database theory book.

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 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 never get into the database! 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.

This post is very bull:

Http://www.itpub.net/viewthread.php?tid=1313696&extra=&page=1

My point is that the foreign key can be added in the initial stage, only disable or drop when forced. When encountering performance bottlenecks, try to tune in other ways, rather than sacrificing foreign keys easily. When there are foreign key constraints, the writing program does have constraints, but intuitively this constraint reveals a certain degree of design or implementation of the unreasonable place. Applications written with foreign keys tend to be more rigorous. Products before the launch if you really need to sacrifice foreign keys to achieve performance optimization, and then pick up the relative unimportant foreign key discarded, at the same time to put this document down, the next time the data inconsistency problem, is a clue. Two point description: 1. One of the projects we're doing is really a small project. 2. I have to admit that I have not developed a relational database for the last three years, seemingly no SQL so NB Key-value pair to save data, in fact, this three years in the persistent layer of a lot of tangled. If I am not right, please correct me!

Here are some insightful ideas to quote:

x support for foreign keys:

1. You may have a bug in your program, and you can judge it by your own judgment, and it will do it quickly and well.
Most people's programs do not consider concurrency issues. Once considered, it has to be manually locked, the efficiency is very low.
Data may be bypassed by your application into the database.
2. Performance issues: Don't you have the overhead of doing it yourself?
A foreign key judgment is apportioned to the transaction level, and the overhead can be ignored and the user is completely unaware.
If you are importing data in bulk, you can temporarily mask the foreign keys, then quickly recover with the novalidate option, provided your data is clean.

It was also mentioned that if 100 tables might need to establish 300 constraints, the performance is poor.
I want to say is still, whether the 300 foreign key constraints are business must, if so, there is no way this is necessary to add, if not, then big can not have to add foreign keys in all places.
If in the program only 5, 6 of the table 10 of the foreign key constraints to judge, and then compared with the 300 foreign keys in the database, and the evaluation of Oracle foreign key performance is too poor, I am afraid it is unfair.

X against the foreign key:

It is true that foreign keys in large systems with very little, in the development of the primary, the design of the database when the general will join the foreign key to ensure the integrity of system design and business requirements, but also for developers to understand the business rules, in the program to control, many large systems in the system stability, will gradually remove the foreign key to ensure performance, Too many features imposed on the database, although the database is very powerful, but after all, many people do not trust the database can be strong enough to do anything to the point. So in a large system of Chinese and foreign key see less is not surprising, small system does not matter, with no foreign key depends on the designer, such a system can be seen everywhere.

Quote another article:

Quoted Http://blog.csdn.net/neusoft_lkz/archive/2009/07/21/4366668.aspx

Whether the database design requires a foreign key. Here are two questions: one is how to ensure the integrity and consistency of database data, and the second is the effect of the first on performance.
Affirmative view:
1, by the database itself to ensure data consistency, integrity, more reliable, because the program is difficult to 100% to ensure the integrity of data, and foreign keys even when the database server or other problems, the data can be guaranteed to the maximum consistency and integrity.
Eg: database and application is a one-to-many relationship, a application will maintain the integrity of his part of the data, when the system becomes larger, the B application is added, A and b two applications may be different development team to do. How do they coordinate the integrity of the data, and if the C application is added a year later?
2, the database design with the primary foreign key can increase the readability of the ER graph, which is very important in database design.
3, the foreign key to a certain extent the business logic, will make the design thoughtful and concrete comprehensive.
Opposing views:
1, you can use a trigger or application to ensure data integrity
2, excessive emphasis or use of the primary key/foreign key will add to the development difficulty, resulting in too many problems such as the table
3, without foreign key data management is simple, easy to operate, high performance (import and export operations, in the INSERT, UPDATE, delete data faster)
Eg: in the vast number of databases do not want to think about foreign keys, imagine, a program to insert millions of records per day, when there are foreign key constraints, each time to scan this record is qualified, generally more than one field has foreign keys, so the number of scans is a series of growth! One of my program storage in 3 hours to finish, if you add foreign keys, it takes 28 hours!

Conclusion:
1, in large-scale systems (performance requirements are not high, high security requirements), the use of foreign keys, in large-scale systems (high performance requirements, security control), no foreign key; small system, the best use of foreign keys.
2, use the foreign key to be appropriate, can not be excessively pursued
3, without foreign keys to control data consistency and integrity, you should write a layer to ensure that then each application through this layer to access the database.

Design principle of primary key and foreign key in database

Http://www.cnblogs.com/tianyamoon/archive/2008/04/02/1134394.html

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 primary keys in a join table that describes many-to-many relationships, and I also oppose this practice because composite primary keys often result in bad foreign keys , that is, when the join table becomes another primary table from the table, And according to the second method above to become part of the primary key of the table, and then, the table may become another table from the main table, its primary key may become another from the table primary key part, so passed, the more 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.

[Reprint] The use of database foreign keys