Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand oracle foreign key constraints. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The method of creating Foreign key constraint

The table-building statement of tes1 is create table test1 (hid number primary key,hname varchar2 (10))

1. Create foreign key constraints while creating the table

1.1, column level

Create table test2 (hid1 number (10) REFERENCES test1 (hid), hname1 varchar2 (10));-- the system automatically generates constraint names

Create table test2 (hid1 number (10) constraint hid_pk REFERENCES test1 (hid), hname1 varchar2 (10))

1.2, table level

Create table test2 (hid1 number (10), hname1 varchar2 (10), foreign key (hid1) REFERENCES test1 (hid));-- the system automatically generates constraint names

Create table test2 (hid1 number (10), hname1 varchar2 (10), constraint hid_pk foreign key (hid1) REFERENCES test1 (hid))

2. Create a foreign key constraint after the table is created

ALTER TABLE test2 ADD FOREIGN KEY (hid1) REFERENCES test1 (hid);-- constraint names are automatically generated by the system

ALTER TABLE test2 ADD CONSTRAINT hid_pk FOREIGN KEY (hid1) REFERENCES test1 (hid)

Errors encountered in child table operations

Cannot modify a record whose value does not exist in the parent table

Cannot insert a record that does not exist in the parent table

Create table test1 (hid number primary key,hname varchar2 (10))

Create table test2 (hid1 number (10) constraint hid_pk REFERENCES test1 (hid), hname1 varchar2 (10))

Insert into test1 values (1)

Insert into test2 values (1dint 100')

Update test2 set hid1=2 where hid1=1;-- error ORA-02291: violation of complete constraint (HR.HID_PK)-parent keyword not found

Insert into test2 values (2 recording 100');-- error ORA-02291: violation of complete constraint (HR.HID_PK)-parent keyword not found

Drop table test2

Drop table test1

Errors encountered in parent table operations

Create table test1 (hid number primary key,hname varchar2 (10))

Create table test2 (hid1 number (10) constraint hid_pk REFERENCES test1 (hid), hname1 varchar2 (10))

Insert into test1 values (1)

Insert into test2 values (1dint 100')

Delete from test1;-- error ORA-02292: violation of complete constraint (HR.HID_PK)-child record found

Truncate table test1;-- error ORA-02266: foreign key reference with unique / primary key enabled in the table

Drop table test1;-- error ORA-02449: the unique / primary key in the table is referenced by the foreign key

Update test1 set hid=2 where hid=1;-- error ORA-02292: violation of complete constraint (HR.HID_PK)-child record found

Create table test1 (hid number primary key,hname varchar2 (10))

Create table test2 (hid1 number (10) constraint hid_pk REFERENCES test1 (hid), hname1 varchar2 (10))

Insert into test1 values (1)

Truncate table test1

Drop table test1;-- error ORA-02266: foreign key reference with unique / primary key enabled in the table

Create table test1 (hid number primary key,hname varchar2 (10))

Create table test2 (hid1 number (10) constraint hid_pk REFERENCES test1 (hid), hname1 varchar2 (10))

Drop table test1;-- error ORA-02266: foreign key reference with unique / primary key enabled in the table

The solution of delete error reporting

Solution 1

Delete from test2

Delete from test1

Solution 2 (do not keep child table records)

Alter table test2 drop constraint hid_pk

ALTER TABLE test2 ADD CONSTRAINT hid_pk FOREIGN KEY (hid1) REFERENCES test1 (hid) ON DELETE CASCADE

Delete from test1

Solution 3 (keep the child table record, but the corresponding field value of the word table becomes null, and the hid1 of the following test2 is null)

Alter table test2 drop constraint hid_pk

ALTER TABLE test2 ADD CONSTRAINT hid_pk FOREIGN KEY (hid1) REFERENCES test1 (hid) ON DELETE SET NULL

Delete from test1

The solution of truncate error reporting

Drop table test2

Truncate table test1

Or

Alter table test1 disable primary key cascade

Truncate table test1

Or

Alter table test1 disable primary key cascade

Truncate table test2

Truncate table test1

An error will be reported if you use the following

Truncate table test2

Truncate table test1;-- continues to report an error ORA-02266: unique / primary key enabled foreign key reference in the table

The solution of drop error reporting

Drop table test1 cascade constraints

Or

Drop table test2

Drop table test1

An error will be reported if you use the following

Alter table test1 disable primary key cascade

Truncate table test2

Drop table test1

The above is the editor for you to share how to understand oracle foreign key constraints, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.