Database primary/foreign key Association

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;

Figure 3-4 foreign key

For foreign keys and links

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.

References:

1. In SQL2000, what are the functions of the five foreign key options in the design table> management relationship?

Check Existing data during creation

When creating a link, apply the link to existing data in the foreign key table. If this dialog box is selected, an error message will notify you of any data that violates the constraints.

Force relationship between insert and update

If this option is selected, only these statements are used to add or update data in the foreign key table.

Force relationship to replication

If this option is selected, the foreign key table is copied to a differentDatabase, Will force the reference integrity of the link.
Cascade update related fields

Whenever the primary key value is updated, it indicates that the database will spread the new key value to the corresponding foreign key field.

Cascade deletion related fields

Indicates when to delete rows in the master table.DatabaseHttp://blog.csdn.net/shanzhiziremove the corresponding line from the external key table.

2. What is the role of Cascade?

On Delete {cascade | no action}

Specifies the action to be taken when the modified row in the table has a reference relationship and the row referenced by the row is deleted from the parent table. The default value is no action.

If cascade is specified, the referenced row will be deleted from the parent table. If no action is specified, SQL Server generates an error and rolls back the row deletion operation in the parent table.

If the on Delete instead of trigger already exists in the table, the On Delete cascade operation cannot be defined.

For example, in the northwind database (http://blog.csdn.net/shanzhizi), there is a reference relationship between the orders table and the MERs table. The foreign key of orders. mermerid references the primary key of customers. customerid.

If you execute the delete statement on a row of the MERs table and it is orders. customerid specifies the on Delete cascade operation, SQL Server checks whether one or more rows are related to the deleted rows in the orders table. If there are related rows, the related rows in the orders table will be deleted along with the referenced rows in the MERs table.

If no action is specified, if at least one row in the orders table references the row to be deleted in the MERs table, SQL Server generates an error and rolls back the delete operation in the customers table.

On update {cascade | no action}

Specifies the operation to be performed on the changed row when the modified row has a reference relationship and the row referenced by the row is updated in the parent table. The default value is no action.

If cascade is specified, the referenced row will be updated in the parent table when the referenced row is updated. If no action is specified, SQL Server generates an error and rolls back the row update operation in the parent table.

If the on Delete instead of trigger already exists in the table, the On Delete cascade operation cannot be defined.

For example, there is a reference relationship between the orders table and the MERs table in the northwind database. The foreign key of orders. mermerid references the primary key of customers. customerid.

If an update statement is executed on a row of the MERs table, and it is orders. customerid specifies the on update cascade operation, SQL Server checks whether there are one or more rows related to the updated rows in the orders table. If related rows exist, the related rows in the orders table are updated with the referenced rows in the MERs table.

If no action is specified, if at least one row in the orders table references the row to be updated in the MERs table, SQL Server raises an error and rolls back the update operation in the customers table.
3. An example of SQL statement used to create a foreign key:

/* Create a database named student_info */
Create Database student_info
/* Use student_info */
Use student_info
Go
/* Create a student table with s_id as the primary key */
Create Table student
(
S_id int identity (1, 1) primary key,
S_name varchar (20) not null,
S_age int
)
Go
/* Create the test table, where test_no is the primary key */
Create Table Test
(
Test_no int identity (1, 1) primary key,
Test_name varchar (30 ),
Nax_marks int not null default (0 ),
Min_marks int not null default (0)
)
Go
/* Create a marks table with s_id and test_no mapped to test_no in the student table and test_no in the test table respectively */
Create Table marks
(
S_id int not null,
Test_no int not null,
Marks int not null default (0 ),
Primary Key (s_id, test_no ),
Foreign key (s_id) References student (s_id ),
Foreign key (test_no) References test (test_no)
)
Go

Reprinted from: http://psp.javaeye.com/blog/573523