Oracle FOREIGN Key--detailed ____oracle

The role of foreign keys:

maintain data consistency and integrity, primarily to control the storage of data in foreign key tables. causes the two tables to be associated, and the foreign key can only refer to the values of the columns in the outer form.

For example:

A B two tables

A table contains the customer number, customer name

Table B contains orders for each customer

After the foreign key is available

You can only delete customer x in table A if you are sure that there are no customer x orders in table B

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

Specify PRIMARY KEY Keywords: foreign key (column name)

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

Event Trigger restrictions: On delete and on update, to set parameter cascade (following foreign key changes), restrict (Restrict foreign key changes in appearance), SET NULL (set NULL value), set default (set default), [default] No action

For example:

Outtable table primary key ID type int

To create a table containing foreign keys:

CREATE TABLE Temp (

ID int,

Name Char (20),

FOREIGN key (ID) References outtable (id) on deletecascade on UPDATE cascade);

Description: Set the ID column as a foreign key reference to the outer outtable ID column when the value of the foreign key deletes the corresponding column in this table screen except when the value of the foreign key changes the corresponding column value in this table.

CASCAD usage: http://blog.csdn.net/kadwf123/article/details/8067381

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

When using the foreign key for who, mainly from the following two points to consider:
1/, delete is how to affect each other, delete the record is constrained by the parent table, which is not bound by the child table;
2/, the record must first exist is the parent table;

Two kinds of uses:
1/, the most commonly used one: reduce duplication of data. Table A has a foreign key, and table B's data is basically not allowed to be deleted. Then select the enforce relationship for INSERT and UPDATE.
2/, Second, is to add a subordinate table. If Table A deletes a record, table B also deletes an associated record in the foreign key relationship, and the primary key of table A is the foreign key of table B. This relationship, in fact table B is the subordinate table of table A (that is, table A is the parent table), when you select the force relation to insert and UPDATE, if you insert data into table B, there must already be a corresponding record in table A. When you select a cascade to delete related fields, deleting one of the records in table a deletes a record in the corresponding table B.

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

When a friend asked me what the role of foreign keys, I also leng a bit, usually in such use, has not really summed up the role of the external key. Below, I summarized the role of the foreign key: A foreign key (FK) is one or more columns that are used to establish and enforce links 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 Save table to another table. This column becomes the foreign key of the second table. The primary 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 ID of the publisher is used when you record information about the book in the titles table, the integrity of the association between the two tables is corrupted, and the publisher's book in the titles table is due to the publishers The data in the table becomes orphaned without a link. FOREIGN KEY constraints prevent this from happening . If changes to the data in the primary key table invalidate the link to the data in the foreign-key table, the change is not implemented, 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 delete the foreign key data or change the foreign key data in the foreign key table, and then link the external key to different primary key data. The foreign key is used to control the data integrity of the data in the database. is when you manipulate the data of a table Data from one or more tables associated with him can be changed at the same time This is the role of the foreign key

[Fine] talk about foreign key

A foreign key (FK) is one or more columns that are used to establish and enforce links 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 Save table to another table. This column becomes the foreign key of the second table.

The primary 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 ID of the publisher is used when you record information about the book in the titles table, the integrity of the association between the two tables is corrupted, and the publisher's book in the titles table is due to the publishers The data in the table becomes orphaned without a link. FOREIGN KEY constraints prevent this from happening. If changes to the data in the primary key table invalidate the link to the data in the foreign-key table, the change is not implemented, 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 delete the foreign key data or change the foreign key data in the foreign key table, and then link the external key to different primary key data.

The foreign key is used to control the data integrity of the data in the database.

is when you manipulate the data of a table

Data from one or more tables associated with him can be changed at the same time

This is the role of the foreign key

Foreign key is an integrity constraint at the database level, which is the database realization way of "referential integrity" mentioned in the basic theory Book of database.

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

For example, there are two tables

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

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

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

As above, b in a is either empty, or a value that exists in B's B, and when there is a foreign key, the database will automatically check to see if a B exists in B.

1. The external expression is referential integrity: this is intrinsic to the data and is not relevant to the program. Therefore, it should be handed over to the DBMS.

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

3. Since we can use triggers or programs to accomplish this work (referring to referential integrity constraints), the DBMS has provided the means for why we have to do it ourselves. And what we do should say that there is no RDBMS to do well. In fact, the early RDBMS didn't have a foreign key, and now it's all there, and I think it makes sense for the database vendor to add this functionality. From this point of view, the foreign key is more convenient.

4, about convenience, according to my project, programmers do have a reflection, mainly in the debugging of the data input trouble: If the data can violate referential integrity, then the referential integrity itself is not the reputation of business conflict, this time should not be used to trigger the futures program; otherwise, the data is wrong, There should be no access to the database at all. Also, this should be a part of the test system: blocking illegal data. In fact, the foreground program should handle this kind of commit failure. Data is an enterprise rather than a program, and the stored procedure should be separated from the data as much as possible, and vice versa.

Finally, several principles of key construction:

1. Create a foreign key for the associated field.

2, all keys must be unique.

3, avoid the use of composite keys.

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

The role of the foreign key.

Foreign key is an integrity constraint at the database level, which is the database realization way of "referential integrity" mentioned in the basic theory Book of database.

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

For example, there are two tables

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

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

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

As above, b in a is either empty, or a value that exists in B's B, and when there is a foreign key, the database will automatically check to see if a B exists in B.

1. The external expression is referential integrity: this is intrinsic to the data and is not relevant to the program. Therefore, it should be handed over to the DBMS.

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

3. Since we can use triggers or programs to accomplish this work (referring to referential integrity constraints), the DBMS has provided the means for why we have to do it ourselves. And what we do should say that there is no RDBMS to do well. In fact, the early RDBMS didn't have a foreign key, and now it's all there, and I think it makes sense for the database vendor to add this functionality. From this point of view, the foreign key is more convenient.

4, about convenience, according to my project, programmers do have a reflection, mainly in the debugging of the data input trouble: If the data can violate referential integrity, then the referential integrity itself is not the reputation of business conflict, this time should not be used to trigger the futures program; otherwise, the data is wrong, There should be no access to the database at all. Also, this should be a part of the test system: blocking illegal data. In fact, the foreground program should handle this kind of commit failure. Data is an enterprise rather than a program, and the stored procedure should be separated from the data as much as possible, and vice versa.

Finally, several principles of key construction:

1. Create a foreign key for the associated field.

2, all keys must be unique.

3, avoid the use of composite keys.

4. A 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 of view is that the foreign key in the initial phase can add, only when forced to disable or drop off. When you encounter performance bottlenecks, try to tune them in other ways, rather than sacrificing the foreign keys easily. When there is a foreign key constraint, the writing program does have constraints, but intuitively this constraint reveals to some extent that the design or implementation of the unreasonable place. With the foreign key to write out the application more inclined to rigorous. Before the product on line if you really need to achieve performance by sacrificing foreign key optimization, and then pick up the relatively unimportant foreign key discarded, at the same time to the document down, the next time you encounter 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 in the last three years, seemingly no SQL so NB Key-value pair data, in fact, this three years in the persistence layer a lot of entanglements. If I'm not right, please correct me.

Here are some insightful ideas to quote:

X supports foreign keys:

1. Your program is also likely to be a serious bug, you can judge your own judgment, it is done quickly and well.
Most people's programs do not consider concurrency issues. Once considered, it has to be hand-locked and inefficient.
Data may bypass your application into the database.
2. Performance problem: Don't you have the overhead to do it yourself.
A foreign key is assessed to the transaction level, the overhead can be ignored, and the user is completely unaware.
If you are importing data in batches, you can temporarily block foreign keys, and 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 would be too poor.
I will say is still, whether these 300 foreign key constraints are business must be, if it is, there is no way this is necessary to add, if not, so large can not be in all places to add foreign keys.
If in the program only 5, 6 tables of 10 or more foreign key constraints to judge, and then the database and 300 foreign keys to compare, and evaluate the performance of the foreign key oracle is too poor, I am afraid it is unfair.

X against the foreign key:

It is true that foreign keys are rarely used in large systems, in the development of the primary, the design of the database will generally add foreign keys, to ensure the integrity of the system design and business requirements of integrity, 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, Put too much functionality 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. So in a large system of foreign key see less is not surprising, small system does not matter, the use of foreign key depends on the design staff, such a system can be seen everywhere.

Another quote:

Quote from 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 impact of the first performance.
Affirmative view:
1, the database itself to ensure data consistency, integrity, more reliable, because the program is difficult to 100% guarantee the integrity of the data, and the use of foreign keys even if the database server when the machine or other problems, can maximize the consistency and integrity of the data.
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 large, increased the B application, A and B two applications may be different development team to do. How do they reconcile data integrity, and if C apps are added a year later?
2, database design with primary foreign key can increase the readability of ER graph, which is very important in database design.
3, the foreign key to a certain extent to explain the business logic, will make the design thoughtful concrete comprehensive.
Opposing views:
1, you can use triggers or applications to guarantee the integrity of the data
2, too much emphasis or use of primary key/foreign key will add to the difficulty of development, resulting in too many tables and other problems
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: don't even think about foreign keys in a massive database, imagine, a program every day to insert millions of records, when there is a 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 programs in storage in 3 hours, if the addition of foreign keys, it will take 28 hours.

Conclusion:
1, in large systems (performance requirements are not high, high security requirements), the use of foreign keys in large systems (high performance requirements, security control), without foreign key, small system, preferably with foreign keys.
2, with the foreign key should be appropriate, not excessive pursuit
3, do not use the foreign key control data consistency and integrity, should write a layer to ensure that each application through this layer to access the database.

design principles for primary and foreign keys in databases

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

Primary and foreign keys are binders that organize multiple tables into a valid relational database. The design of primary keys and foreign keys has a decisive impact on the performance and availability of physical databases.

The database schema must be converted from theoretical logical design to actual physical design. And the structure of primary key and foreign key is the crux of this design process. Once the designed database is used in the production environment, it is difficult to modify these keys, so it is necessary and worthwhile to design primary and foreign keys at the development stage.

Primary key:

A relational database relies on a primary key---it is the cornerstone of the physical schema of the database. Primary keys are only available on a physical level for two purposes:

1. Uniquely identifies a row.

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

Based on these two uses, here are some of the principles I follow when designing a primary key at the physical level:

1. The primary key should be