How to Set primary keys and Foreign keys for Cascade update and cascade Deletion

Source: Internet
Author: User

What are the differences between primary keys, foreign keys, and indexes?

  Primary Key Foreign key Index
Definition: Uniquely identifies a record. The record must be unique and cannot be blank. The foreign key of the table is the primary key of the other table. The foreign key can have duplicate values and can be null values. This field does not have repeated values, but it may have a null value.
Purpose: Used to ensure data integrity Used to establish connections with other tables Is to increase the speed of query sorting
Quantity: Only one primary key can exist. A table can have multiple foreign keys. A table can have multiple unique indexes.

Set Indexes

To set a foreign key, you must set indexes for both the reference table (pc table) and the referenced table (parts table ).

For the parts table:

Alter table parts add index idx_model (model );
This statement adds an index to the parts table, which is created on the model Field and named idx_model.

Similar to pc tables:

Alter table pc add index idx_cpumodel (cpumodel );

Define Foreign keys

The following describes how to create a constraint between two tables ". Because the CPU model of the pc must refer to the corresponding model in the parts table, we set the cpumodel field of the pc table to "foreign key" (foreign key ), that is, the reference value of this key comes from other tables.

Alter table pc add constraint fk_cpu_model

Foreign key (cpumodel)

REFERENCES parts (model );

Cascade operations

Cascade update: when the primary key is updated, the foreign key is also updated.

When defining a foreign key, you can add such a keyword at the end:
On update cascade;

That is to say, when the primary table is updated, the sub-table (s) generates a chain update action. It seems that some people like to call this "cascade" operation.

If the statement is completely written, it is:

Alter table pc add constraint fk_cpu_model

Foreign key (cpumodel)

REFERENCES parts (model)

On update cascade;

Cascading deletion: When a primary key is deleted, the foreign key is also deleted.

Alter table pc add constraint fk_cpu_model

Foreign key (cpumodel)

REFERENCES parts (model)

On delete cascade;

Cascade update and delete:

 on update cascade on delete cascade

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.