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.
|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
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.
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
// 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.
SQL> truncate table tbl_a;Table truncated.
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
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.