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

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