How to quickly understand the options for creating foreign keys in a DB2 database

Options for creating a foreign key:

1. Create a test table:

Drop table student;
Drop table class;
Drop table student_class;
Create table student (student_id integer not null, student_name varchar (200), CONSTRAINT P_KEY_1 primary key (student_id) in luzl_32k_tb index in luzl_32k_tb;
Create table class (class_id integer not null, class_name varchar (200), CONSTRAINT P_KEY_2 primary key (class_id) in luzl_32k_tb index in luzl_32k_tb;
Create table student_class (student_class_id integer, student_id integer, class_id integer) in luzl_32k_tb index in luzl_32k_tb;
Alter table student_class add constraint if_class foreign key (class_id) references class (class_id) on delete cascade on update restrict;
Alter table student_class add constraint if_student foreign key (student_id) references student (student_id) on delete cascade on update restrict;
Insert into student (student_id, student_name) values (1, 'luzl ');
Insert into class (class_id, class_name) values (1, 'db2 ');
Insert into student_class (student_class_id, student_id, class_id) values (1, 1, 1 );

2. Options for On Delete are:

Restrict/no action/cascade/set null. if the cascade option is specified, if the parent record is deleted, its child record will be deleted automatically. it is equivalent to cascading deletion. if no action or cascade is specified, an error is returned. As there are sub-records, this record cannot be deleted. set nul allows you to delete a parent record. l sets the field associated with the parent table to null.

3. On Update only has two options: no action/restrict. They are no different during Update and deletion: if they are associated with a sub-table, they cannot be deleted.

4. Note that the fields in the parent table must be primary keys before they can be used as foreign keys of the child table.

Some of the introductions in this article are presented in the form of code, which may be difficult for beginners to understand. This article is more organized for everyone, hope to help everyone.