外來鍵約束的建立方法
tes1的建表語句為create table test1 (hid number primary key,hname varchar2(10));
1、建立表的同時建立外來鍵約束
1.1、列級別
create table test2 (hid1 number(10) REFERENCES test1(hid),hname1 varchar2(10));--系統自動生成約束名
create table test2 (hid1 number(10) constraint hid_pk REFERENCES test1(hid),hname1 varchar2(10));
1.2、表級別
create table test2 (hid1 number(10) ,hname1 varchar2(10),foreign key (hid1) REFERENCES test1(hid));--系統自動生成約束名
create table test2 (hid1 number(10) ,hname1 varchar2(10)，constraint hid_pk foreign key (hid1) REFERENCES test1(hid));

2、表建立後再建立外來鍵約束
ALTER TABLE test2 ADD FOREIGN KEY (hid1) REFERENCES test1 (hid);--系統自動生成約束名
ALTER TABLE test2 ADD CONSTRAINT hid_pk FOREIGN KEY (hid1) REFERENCES test1 (hid);

子表操作會遇到的報錯
不能修改值為父表不存在的記錄
不能插入父表不存在的記錄
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,'1');
insert into test2 values(1,'100');
update test2 set hid1=2 where hid1=1;--報錯ORA-02291: 違反完整約束條件 (HR.HID_PK) - 未找到父項關鍵字
insert into test2 values(2,'100');--報錯ORA-02291: 違反完整約束條件 (HR.HID_PK) - 未找到父項關鍵字
drop table test2;
drop table test1;

父表操作遇到的報錯
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,'1');
insert into test2 values(1,'100');
delete from test1;--報錯ORA-02292: 違反完整約束條件 (HR.HID_PK) - 已找到子記錄
truncate table test1;--報錯ORA-02266: 表中的唯一/主鍵被啟用的外來鍵引用
drop table test1;--報錯ORA-02449: 表中的唯一/主鍵被外來鍵引用
update test1 set hid=2 where hid=1;--報錯ORA-02292: 違反完整約束條件 (HR.HID_PK) - 已找到子記錄

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,'1');
truncate table test1;
drop table test1;--報錯ORA-02266: 表中的唯一/主鍵被啟用的外來鍵引用

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;--報錯ORA-02266: 表中的唯一/主鍵被啟用的外來鍵引用

delete報錯的解決方法
解決方法1
delete from test2;
delete from test1;

解決方法2(不保留子表記錄)
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;

解決方法3(保留子表記錄，但是字表對應欄位值變成null，如下test2的hid1為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;

truncate報錯的解決方法
drop table test2;
truncate table test1;
或
alter table test1 disable primary key cascade;
truncate table test1;
或
alter table test1 disable primary key cascade;
truncate table test2;
truncate table test1;

採用如下一樣會報錯
truncate table test2;
truncate table test1;--繼續報錯ORA-02266: 表中的唯一/主鍵被啟用的外來鍵引用

drop報錯的解決方法
drop table test1 cascade constraints;
或
drop table test2;
drop table test1;

採用如下一樣會報錯
alter table test1 disable primary key cascade;
truncate table test2;
drop table test1;

oracle外來鍵約束的總結

相關文章