[ZT] foreign key (FK)

Use of Foreign database keys

 

 

Foreign key:

Data consistency and integrity are maintained to control data stored in the foreign key table. Associate two tables. The foreign key can only reference the values of columns in the External table!

For example:

A and B

Table A contains the customer ID and customer name.

Orders of each customer exist in table B.

With the foreign key

You can only delete Customer X in Table A after you confirm that there is no Customer X order in table B.

Prerequisites for creating a foreign key: the columns in the table must be of the same type as the foreign key (the foreign key must be the external primary key ).

Specify the primary key Keyword: foreign key (column name)

Reference foreign key Keyword: References <foreign key table name> (foreign key column name)

Event trigger restrictions: On Delete and on update. You can set the parameters cascade, restrict, and set null ), set default (Set default value), [Default] no action

For example:

Outtable table primary key ID type int

Create a table with foreign keys:

Create Table temp (

Id int,

Name char (20 ),

Foreign key (ID) References outtable (ID) on Delete cascade on update cascade );

Note: set the ID column as the foreign key reference External table ID column as the foreign key value Delete this table corresponding to the column sieve when the foreign key value changes the corresponding column value in this table.

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

Who serves as the foreign key when using it mainly takes into account the following two points:
1. How does the deletion affect each other? The parent table is restricted to the deletion record, and the child table is not restricted;
2/, the record must first exist in the parent table;

Two purposes:
1/, the most common one: reduce repeated data. Table A has a foreign key, and table B's data is basically not allowed to be deleted.Force relationship between insert and updateYou can.
2/, followed by adding a subordinate table. if table A deletes a record and table B also deletes an associated record, the primary key of Table A is the foreign key of Table B in the foreign key relationship. In fact, table B is the subordinate table of Table A (that is, Table A is the parent table). SelectForce relationship between insert and updateIf data is inserted into Table B, a corresponding record must exist in table. SelectCascade deletion related fieldsWhen you delete a record in Table A, a record in Table B is deleted.

 

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

When a friend asked me what the role of the foreign key is, I also paused. It is usually used in this way. I haven't really summarized it yet. What is the role of the foreign key. below, I have summarized the role of foreign keys: A foreign key (FK) is one or more columns used to establish and enhance the link between two table data. You can create a link between two tables by adding one or more columns of the primary key values in the table to another table. This column becomes the foreign key of the second table. The 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 a publisher is deleted from the publishers table and the publisher's ID is used to record the book information in the titles table, the integrity of the association between the two tables will be damaged, in the titles table, the publisher's books are isolated because they have no links to the data in the publishers table. Foreign key constraints prevent this situation. If the data changes in the primary key table make the link to the data in the foreign key table invalid, this change cannot be implemented, thus ensuring the integrity of the reference. If you try to delete a row in the primary key table or change the primary key value, and the primary key value is related to the foreign key constraint value of the other table, this operation cannot be implemented. To successfully modify or delete the row with the foreign key constraint, You can first delete or change the foreign key data in the foreign key table, and then link the foreign key to different primary key data. Foreign keys are used to control data integrity in the database. That is, when you operate on the data of a table The data of one or more tables associated with the table can change at the same time. This is the role of the foreign key.

 

 

 

[Excellent] Foreign keys

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

The 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 a publisher is deleted from the publishers table and the publisher's ID is used to record the book information in the titles table, the integrity of the association between the two tables will be damaged, in the titles table, the publisher's books are isolated because they have no links to the data in the publishers table. Foreign key constraints prevent this situation. If the data changes in the primary key table make the link to the data in the foreign key table invalid, this change cannot be implemented, thus ensuring the integrity of the reference. If you try to delete a row in the primary key table or change the primary key value, and the primary key value is related to the foreign key constraint value of the other table, this operation cannot be implemented. To successfully modify or delete the row with the foreign key constraint, You can first delete or change the foreign key data in the foreign key table, and then link the foreign key to different primary key data.

Foreign keys are used to control data integrity in the database.

That is, when you operate on the data of a table

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

This is the role of the foreign key.

 

Foreign keys are integrity constraints at the database level. They are the database implementation method of "Reference integrity" as described in the basic database theory.

Foreign key attributes can certainly be removed. If you don't want to use these constraints, it will certainly not affect programming, however, the "reference integrity" check is not performed for the input data.

For example, there are two tables

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

B (B, c, d): B is the primary key

If I remove the foreign key attribute of field B, it will not affect programming.

As shown above, B in a is either empty or a value in B's B. When a foreign key exists, the database automatically checks whether B of A exists in B.

1. External construction expresses the integrity of reference: this is inherent in the data and has nothing to do with the program. Therefore, it should be handed over to the DBMS.

2. External building is simple and intuitive, and can be directly reflected in the data model. It has great benefits in terms of design, maintenance, and other backtracing, especially when analyzing the benefits of existing databases, it was obvious that I recently analyzed an existing enterprise database. The reference integrity constraints in the database are foreign key descriptions, and some are implemented using triggers, obviously. Of course, there may be but not all in the document, but the foreign key is very obvious and intuitive.

3. Since we can do this with triggers or programs (referring to integrity constraints), DBMS has provided a means. Why should we do it ourselves? What we do should be said that no RDBMS is doing well. In fact, the early RDBMS did not have any foreign keys, and now it is available. I think it makes sense for the database vendor to add this function. From this perspective, foreign keys are more convenient.

4. For convenience, according to my project, the programmer does reflect the trouble of inputting data during debugging: if the data violates the integrity of the reference, that is to say, the integrity of the reference itself does not conflict with the reputation business. At this time, it should not be implemented by triggering the Futures program; otherwise, it indicates that the data is wrong and should not be written into the database! In addition, this should also be a part of the test system: Blocking illegal data. In fact, the foreground program should handle such submission failures. Data is from an enterprise rather than a program. The stored program should be separated from the data whenever possible, and vice versa.

Finally, the key creation principles are as follows:

1. Create a foreign key for the associated field.

2. All keys must be unique.

3. Avoid using compound keys.

4. Foreign keys are always associated with unique key fields.

 

What is the role of a foreign key?

Foreign keys are integrity constraints at the database level. They are the database implementation method of "Reference integrity" as described in the basic database theory.

Foreign key attributes can certainly be removed. If you don't want to use these constraints, it will certainly not affect programming, however, the "reference integrity" check is not performed for the input data.

For example, there are two tables

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

B (B, c, d): B is the primary key

If I remove the foreign key attribute of field B, it will not affect programming.

As shown above, B in a is either empty or a value in B's B. When a foreign key exists, the database automatically checks whether B of A exists in B.

1. External construction expresses the integrity of reference: this is inherent in the data and has nothing to do with the program. Therefore, it should be handed over to the DBMS.

2. External building is simple and intuitive, and can be directly reflected in the data model. It has great benefits in terms of design, maintenance, and other backtracing, especially when analyzing the benefits of existing databases, it was obvious that I recently analyzed an existing enterprise database. The reference integrity constraints in the database are foreign key descriptions, and some are implemented using triggers, obviously. Of course, there may be but not all in the document, but the foreign key is very obvious and intuitive.

3. Since we can do this with triggers or programs (referring to integrity constraints), DBMS has provided a means. Why should we do it ourselves? What we do should be said that no RDBMS is doing well. In fact, the early RDBMS did not have any foreign keys, and now it is available. I think it makes sense for the database vendor to add this function. From this perspective, foreign keys are more convenient.

4. For convenience, according to my project, the programmer does reflect the trouble of inputting data during debugging: if the data violates the integrity of the reference, that is to say, the integrity of the reference itself does not conflict with the reputation business. At this time, it should not be implemented by triggering the Futures program; otherwise, it indicates that the data is wrong and should not be written into the database! In addition, this should also be a part of the test system: Blocking illegal data. In fact, the foreground program should handle such submission failures. Data is from an enterprise rather than a program. The stored program should be separated from the data whenever possible, and vice versa.

Finally, the key creation principles are as follows:

1. Create a foreign key for the associated field.

2. All keys must be unique.

3. Avoid using compound keys.

4. Foreign keys are always associated with unique key fields.

 

 

 

 

This post is awesome:

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

 

My point is,The Foreign keys can be added in the initial stage. They can be disable or dropped only when they are forced. When encountering performance bottlenecks, try to use other optimization methods instead of sacrificing the foreign key easily. When there are foreign key constraints, write programs do have constraints, but intuitively this constraint reveals unreasonable design or implementation. Applications written with foreign keys tend to be rigorous. Before the product is launched, if you really need to optimize the performance by sacrificing the foreign key, and then discard the less important foreign key, at the same time, you need to remove this document, the next time you encounter data inconsistency, It is a clue. Two notes: 1. A project we are working on is indeed a small project. 2. I have to admit that I have not used relational databases in the last three years. It seems that no SQL is used to store Nb key-value pair data. In fact, these three years have been a lot of tangle on the persistence layer. If I am not correct, please correct me!

 

The following are some insightful ideas:

× Foreign key supported:

1. If your program is more rigorous, you may also encounter bugs. If you decide on your own, you should leave it to the database for judgment. It is fast and good.
Most people do not consider concurrency issues. Once you have considered it, you have to manually lock it, and the efficiency is very low.
Data may bypass your application and enter the database.
2. Performance problems: Is there no overhead if you do it yourself?
When a foreign key is determined to be apportioned to the transaction level, the overhead can be ignored, and the user is not aware of it at all.
If you want to import data in batches, You can temporarily block the foreign key and use the novalidate option to quickly restore it, provided that your data is clean.

 

It is also mentioned that 100 constraints may be required for 300 tables, resulting in poor performance.
I still want to talk about whether these 300 foreign key constraints are mandatory for the business. If so, there is no way to add them. If not, otherwise, you do not need to add Foreign keys in all places.
If the program only judges 10 foreign key constraints for five or six tables, and then compares them with the 300 foreign keys in the database, I am afraid it is unfair to evaluate Oracle's foreign key performance.

 

× Foreign key opposition:

It is true that foreign keys are rarely used in large systems. during initial development and database design, foreign keys are generally added to ensure the integrity of system design and business needs, it also facilitates developers to understand business rules and control them in programs. Many large systems gradually remove foreign keys after the system is stable to ensure performance and impose too many functions on the database, although the database is powerful, after all, many people do not trust the database to be powerful enough to do anything. Therefore, it is not surprising that foreign keys are rarely seen in a large system. Small systems do not matter. The use of foreign keys is determined by the designers. Such systems can also be seen everywhere.

 

 

Another article:

Self-built http://blog.csdn.net/neusoft_lkz/archive/2009/07/21/4366668.aspx

Whether foreign keys are required for database design. There are two problems: one is how to ensure the integrity and consistency of database data; the other is the impact of the first article on performance.
Fang viewpoint:
1. The database itself ensures data consistency, integrity, and reliability, because it is difficult for programs to guarantee data integrity by 100%, the use of foreign keys can ensure data consistency and integrity to the maximum extent even when the database server is running or has other problems.
Eg: the relationship between the database and the application is one-to-many. application a maintains the integrity of its data. When the system changes, application B is added, applications A and B may be developed by different development teams. How can they coordinate and ensure data integrity? If another C application is added one year later?
2. Database Design with primary and Foreign keys can increase the readability of the ERTU, which is very important in database design.
3. the business logic described by the foreign key to a certain extent will make the design thoughtful and comprehensive.
Opposing point of view:
1. You can use triggers or applications to ensure data integrity.
2. Over-emphasizing or using primary/Foreign keys makes development difficult, resulting in too many tables and other problems.
3. easy data management without foreign keys, convenient operations, and high performance (import and export operations are faster during insert, update, and delete operations)
Eg: Do not think about foreign keys in a massive database. Imagine that a program needs to insert millions of records every day. When there is a foreign key constraint, each time you want to scan this record, whether the record is qualified. Generally, more than one field has a foreign key, so that the number of scans increases in the number of levels! One of my programs has been stored in the database for three hours. If a foreign key is added, it will take 28 hours!

Conclusion:
1. In large systems (with low performance requirements and high security requirements), foreign keys are used. In large systems (with high performance requirements and self-controlled security), foreign keys are not required; the foreign key is recommended for small systems.
2. Foreign keys should be used properly and should not be excessively pursued
3. When using a program to control data consistency and integrity without foreign keys, write a layer to ensure that each application accesses the database through this layer.

 

 

 

Primary Key and foreign key design principles in the database

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

 

The primary key and foreign key are the adhesives that organize multiple tables into a valid relational database. The primary key and foreign key design have a decisive impact on the performance and availability of the physical database.

The database mode must be converted from the logical design to the physical design. The structure of the primary key and the foreign key is the crux of this design process. Once the database is used in the production environment, it is difficult to modify these keys. Therefore, it is necessary and worthwhile to design the primary keys and Foreign keys during the development stage.

Primary key:

Relational databases depend on primary keys. It is the cornerstone of the physical database mode. The primary key has only two purposes on the physical layer:

1. uniquely identifies a row.

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

Based on the above two purposes, the following provides some principles that I follow when designing the primary keys of the physical layer:

1. The primary key should beMeaningless to users. If you see data in a connection table that represents many-to-many relationships and complain about its usefulness, it proves that its primary key is well designed.

2. The primary key should beSingle ColumnTo improve the efficiency of connection and filtering operations.

Note:People who use the composite key usually have two reasons for self-release, both of which are incorrect. The first is that the primary key should have practical significance. However, making the primary key meaningful only makes it easy to artificially damage the database. The second is that two external keys can be used as the primary key in the connection table describing multiple-to-multiple relationships. I also oppose this approach because:Compound primary keys often lead to bad Foreign keysThat is, when the connected table becomes another master table of the slave table and becomes a part of the primary key of the table according to the second method above, however, this table may become another master table of another slave table, and its primary key may be a part of another primary key of the slave table. If this is passed on, the closer it is to the back of the slave table, the primary key will contain more columns.

3.Never update the primary key. In fact, because the primary key only identifies a row and has no other purposes, there is no reason to update it. If the primary key needs to be updated, it indicates that the primary key should be meaningless to the user.

Note:This principle is not applicable to data that often requires data transformation or multi-database consolidation.

4.Primary keys should not contain dynamically changed dataSuch as the timestamp, Creation Time column, and modification time column.

5.The primary key should be automatically generated by a computer.. If a person intervene in the creation of a primary key, it will have a meaning other than a unique row. Once this boundary is crossed, the motive for modifying the primary key may be generated, this system is used to link record rows and manage record rows. It will fall into the hands of people who do not know the database design.