DB2 foreign key creation options

Source: Internet
Author: User
Tags db2 foreign key

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;

 
 
  1. drop table class;   
  2. drop table student_class;   
  3. 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 ;   
  4. 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 ;   
  5. Create table student_class(student_class_id integer,student_id integer,class_id integer) in luzl_32k_tb index in luzl_32k_tb;   
  6. alter table student_class add constraint if_class foreign key(class_id) references class(class_id) ON DELETE cascade ON UPDATE RESTRICT;   
  7. alter table student_class add constraint if_student foreign key(student_id) references student(student_id) ON DELETE cascade ON UPDATE RESTRICT;   
  8. Insert into student(student_id,student_name) values(1,'luzl');   
  9. Insert into class(class_id,class_name) values(1,'db2');   
  10. 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.

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.