How to do the truncate operation for a primary table associated with a foreign key

Source: Internet
Author: User

How to do the truncate operation for a primary table associated with a foreign key

The primary and Foreign keys are mandatory associations between two tables provided by the database. They can also be implemented at the application layer.

  Advantages
Disadvantages
Primary and Foreign keys implemented by the database
Guaranteed by the database layer mechanism, no additional application implementation is required
Strong Association, difficult to expand changes
Main foreign key for application implementation
Easy scaling

Fully controlled by applications, with high requirements

I think it is necessary to make a trade-off based on the actual situation. For example, tables are not complex and can be implemented by the application. If the association between tables is large and complex, it should be handled by the database, at least to ensure that there is no error.

A table with a primary or foreign key association exists. Some operations, such as truncate, are prohibited due to a foreign key Association.

Lab

1. Create a test table
SQL> create table tbl_a (id number, remark varchar2 (1 ));
Table created.

SQL> create table tbl_ B (id number, a_id number, remark varchar2 (1 ));
Table created.

SQL> alter table tbl_a add constraint pk_tbl_a primary key (id );
Table altered.

SQL> alter table tbl_ B add constraint pk_tbl_ B primary key (id );
Table altered.

SQL> alter table tbl_ B add constraint fk_tbl_ B _a foreign key (a_id) references tbl_a (id );
Table altered.
 
Tbl_a is the primary table, tbl_ B is the sub table, and associated with tbl_a.

2. Currently, no data exists in the master table and sub-table, and the truncate master table is executed.

SQL> truncate table tbl_a;
Table truncated.
Yes.

3. Insert a record to the master table and execute truncate again.
SQL> insert into tbl_a values (1, 'A ');
1 row created.

SQL> commit;
Commit complete.

SQL> truncate table tbl_a;
Truncate table tbl_a
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
 
A ORA-02266 is prompted: A unique/primary key-enabled foreign key reference
 

Look at the ORA-02266 explanation:

02266,000 00, "unique/primary keys in table referenced by enabled foreign keys"
// * Cause: An attempt was made to truncate a table with unique or
// Primary keys referenced by foreign keys enabled in another table.
// Other operations not allowed are dropping/truncating a partition of
// Partitioned table or an alter table exchange partition.
// * Action: Before encounter the above operations the table, disable
// Foreign key constraints in other tables. You can see what
// Constraints are referencing a table by issuing the following
// Command:
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam ";

The problem and solution are described clearly: you can disable the foreign key constraint before executing the command, and then restore the foreign key constraint after executing the truncate command.

4. Disable the foreign key constraint and perform the Restoration Operation after deletion.

The foreign key constraint name is FK_TBL_ B _A:
SQL> select constraint_name, constraint_type, status from user_constraints where table_name = 'tbl _ B ';

CONSTRAINT_NAME C STATUS
---------------------------------------
PK_TBL_ B P ENABLED
FK_TBL_ B _A R ENABLED

 


Disable foreign key constraints:
SQL> alter table tbl_ B disable constraint FK_TBL_ B _A;

Table altered.

SQL> select constraint_name, constraint_type, status from user_constraints where table_name = 'tbl _ B ';

CONSTRAINT_NAME C STATUS
---------------------------------------
PK_TBL_ B P ENABLED
FK_TBL_ B _A R DISABLED

The STATUS changes to DISABLED.

Truncate table:
SQL> truncate table tbl_a;

Table truncated.

Recovery constraints:
SQL> alter table tbl_ B enable constraint FK_TBL_ B _A;

Table altered.

SQL> select constraint_name, constraint_type, status from user_constraints where table_name = 'tbl _ B ';

CONSTRAINT_NAME C STATUS
---------------------------------------
PK_TBL_ B P ENABLED
FK_TBL_ B _A R ENABLED

Summary:
 
1. the primary foreign key is a strong constraint provided by the database. It can help us control the relationship between the primary and sub-tables, but it is also a double-edged sword. Of course, we think that since the primary foreign key is defined, this forced relationship is required, but sometimes there may be some changes. Therefore, we need to make decisions based on the actual situation.

2. in the primary table associated with the primary foreign key, if there is data, you cannot directly delete it using the truncate method, because it is considered that there is a foreign key associated with it, and you cannot directly cut the primary table. If you need to do this, you can disable the foreign key constraint first, and the master table becomes an independent table, so that you can execute truncate.

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.