Quick understanding of the Operation solution for the options when creating a foreign key for DB2

The following articles mainly show you how to quickly master the options for creating foreign keys in DB2, including creating a test table correctly and introducing the On Delete option, the following describes the main content of the article.

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. The above content is an introduction to the options for quick understanding of the DB2 foreign key creation. I hope you will gain some benefits.

The above content describes the options used to create Foreign keys for DB2. We hope to help you in this regard.