DB2 foreign key creation options

The following articles describe how to quickly master the options for creating foreign keys in DB2, including creating test tables and introducing On Delete options, the following is a detailed description of the main content of the article. I hope you will have a better understanding of it after browsing.

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 describes how to quickly master the options when DB2 creates a foreign key. I hope it will help you in this regard.