Foreign keys and cascading

About the foreign key write good URL: http://blog.csdn.net/lidaasky/article/details/7645524.

• FOREIGN key
  • Defined
  • Role
  • Main Table and from table
  • Key Building Principles
  • Event Trigger Limit
• A simple instance of a foreign key
• Trigger implementation
  • Build a Table statement
  • Relationship Description
  • Implementing Cascade Deletions
• Change the settings implementation
• Summarize

Wen Shou

Today to test system to add student information failure, because student and classes table has a cascade relationship, as from the table student table can not be arbitrarily added, these have learned but not how to use, take this opportunity to learn.

FOREIGN key

when it comes to cascade deletion, we have to say the foreign key, the definition of the foreign key: "If the public key is the primary key in a relationship, then this common keyword is called the foreign key of another relationship; in other words, if a property set in relationship mode R is not the primary key of R, But the primary key of another relationship R1 the set is the foreign key of the relational mode R.  

To create a foreign key SQL statement:

[SQL]View Plaincopy

1. Foreign Key(IdB)ReferencesA (IdA)

[SQL]View Plaincopy

1. foreign key(IdB) references A (idA)

As you can see from syntax analysis, a foreign key is a reference relationship between a table's field values .

Main Table and from table

Main table and from table: A table with a foreign key of another relationship as the primary key is referred to as the table , and a table with a key in it is called the primary table. To be blunt: the primary table is the referenced table, and the appearance is the table that references the other table .

So what's the role of the foreign key? The foreign key function can be said to be: maintain data consistency, integrity, relevance, the main purpose is to control the data stored in the Foreign key table. is when you manipulate the data of a table, and the data of one or more tables associated with him can change at the same time, avoiding invalid or useless changes and orphaned data .

Key Building Principles :

1. The Foreign key field is the primary key
2. All keys must be unique
3. Avoid using composite keys
4. Foreign keys are always associated with unique key fields

FOREIGN KEY constraint event firing . Since there is a foreign key constraint, what happens when a constraint is violated? We think of its reaction as an event, a total of four kinds:

1. Cascading: When the primary table changes, follow the changes from the table.

2. Do not perform any operation.

3, SET NULL.

4. Set the default value.

definition of primary table, table, and foreign key: I lent you my notebook, I can think of "I" as the main Table, "notebook" as a foreign key, "you" as a form, that is, "you" in the Use (reference) "I" "Notebook";

The role of the foreign key is: Now you want to sell my notebook to throw a person (add update delete, etc.), I have to see my permission not to allow, I want to move away, you do not give me the notebook, I can not move;

Key principles: Before borrowing first we have to say, I have only such a notebook, you also better also only borrowed my this notebook, avoid you also borrowed someone's notebook, do not know which is mine.

Event Trigger: I said you hurriedly give back my notebook, you can choose to return to me, can choose not to respond to me and so on. In a word, because of this notebook, "You" and "I" related, this notebook how, cannot one person to decide.

Cascade Delete

But what if I want to cascade delete?

SQL statements Modify settings : Because the default foreign key trigger is "do nothing", we can use the SQL statement to change the settings:

[SQL]View Plaincopy

1. FOREIGN KEY(ID)REFERENCESTabley (ID) on Delete Cascade  on Update Cascade   

[SQL]View Plaincopy

1. FOREIGN KEY(ID)REFERENCESTabley (ID) on Delete Cascade  on Update Cascade   

This means that the table will follow the change of the main table.

Foreign keys and cascading