How to do a master table with a foreign key Association

Source: Internet
Author: User

How to do a master table with a foreign key Association

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 ENABLEDFK_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 ENABLEDFK_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 ENABLEDFK_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

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.