Explain the cascading deletion in SQL and Oracle foreign KEY constraints

Source: Internet
Author: User

In the recent software system to delete a record, it is necessary to link to delete many tables at the same time, there is a constraint relationship between them. So consider adding a constraint relationship when creating a table, and the details are as follows:

The foreign KEY constraint of SQL can realize cascading deletion and cascade updating;

Oracle is only allowed to cascade Delete.

SQL cascade deletion and cascading updates using the format:

CREATE TABLE A001(ID INT PRIMARY KEY,NAME VARCHAR(20))
CREATE TABLE A002(ID INT REFERENCES A001(ID)ON DELETE CASCADE ON UPDATE CASCADE,AGE TINYINT)
ORACLE级联删除使用格式:
CREATE TABLE A001(ID INT PRIMAY KEY,NAME VARCHAR2(20))
CREATE TABLE A002(ID INT REFERENCES A001(ID)ON DELETE CASCADE,AGE NUMBER(2,0) )
--------------
CREATE TABLE groups
(
id VARCHAR2(16) CONSTRAINT pk_groupid PRIMARY KEY,
name VARCHAR2 (32),
description VARCHAR2(50)
)
TABLESPACE userspace;
CREATE TABLE usringrp
(
group_id VARCHAR2(16) CONSTRAINT fk_uing_grpid
REFERENCES groups(id)
ON DELETE CASCADE,
user_id VARCHAR2(16)
)
TABLESPACE userspace;
---------------
PowerDesigner

Referential integrity constraints

Restrictions (Restrict). Modify or delete operations are not allowed. If you modify or delete a primary key for a primary table, if a child record exists in the child table, the system will produce an error prompt. This is the default referential integrity setting.

Empty (Set null). If the foreign key column allows NULL, the foreign key column referenced in the table is set to a null value if the primary key of the primary table is modified or deleted.

To default (Set default). If you specify a default value, the foreign key referenced in the hash table is set to the default value (default) If you modify or delete the primary key for the primary table.

Cascade (Cascade). When you modify a primary key in the primary table to a new value, you modify the value of the foreign key in the child table, or delete the records for the primary key in the primary table, deleting the foreign key from the child table.

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.