Oracle外键约束reference,表的约束--外键约束 foreign key reference

1 外键约束

一个表中某字段的值，受另一张表中某个字段的限制

主表(父表)：提供数据的表

从表(子表)：外键所在的表(引用主表中唯一性字段(主健，唯一)的值)

外键的值只能是主表中对应字段的值或者为null

外键约束的语法(分为列级和表级，起名字和没有名字的)

foreign key

references 主表(字段) --无名

constraint 约束名

foreign key references 主表(字段) -- 有名

foreign key

references 主表(字段)

constraint 约束名

foreign key references 主表(字段)

1.1 创建表

一般先创建主表，再创建从表。

//创建主表

create table

parent_lzh_88(

id

number(7) primary key,

name

varchar2(25) not null

);

//创建子表

create table

child_lzh_88(

id number

primary key,

c_name

varchar2(20) not null,

p_id

number(7) references parent_lzh_88(id)

);

1.2

数据操作语句(dml)

对于有外键约束的表，执行dml操作时，一定要确保子表中外键的值不能孤立(在主表中可以找到)

1)insert

insert into parent_lzh_88 values(1,

'Admin');

commit;

insert into child_lzh_88 values(1001, 'test1',

1);

insert into child_lzh_88 values(1002, 'test2',

1);

commit;

2)update

update child_lzh_88 set p_id = 4 where id =

1002

*

ERROR at line 1:

ORA-02291: integrity constraint

(OPENLAB.SYS_C0027571) violated - parent key

not found

update parent_lzh_88 set id=4 where

id=1

*

ERROR at line 1:

ORA-02292: integrity constraint

(OPENLAB.SYS_C0027571) violated - child record

found

3)delete

delete from parent_lzh_88 where id=1

*

ERROR at line 1:

ORA-02292: integrity constraint

(OPENLAB.SYS_C0027571) violated - child record

found

1.3 删除表

一般先删除子表，再删除主表

drop table parent_lzh_88

*

ERROR at line 1:

ORA-02449: unique/primary keys in table

referenced by foreign keys

drop table child_lzh_88;

drop table parent_lzh_88;

--对表的级联删除(先解除外键约束，然后再删除表)

drop table 表名 cascade constraints;

SQL> drop table parent_lzh_88 cascade

constraints;

Table dropped.

1.4

表中数据的级联删除和级联置空

在添加外键的时候添加

级联删除：on delete cascade

级联置空：on

//创建主表

create table

parent_lzh_88(

id

number(7) primary key,

name

varchar2(25) not null

);

//创建子表

create table

child_lzh_88(

id number

primary key,

c_name

varchar2(20) not null,

p_id

number(7),

constraint

parent_id_child_pid_fk_lzh_88 foreign key(p_id) references

parent_lzh_88(id) on delete cascade

);

insert into parent_lzh_88 values(1,

'Admin');

insert into parent_lzh_88 values(2,

'test');

insert into child_lzh_88 values(1001, 'l',

1);

insert into child_lzh_88 values(1002, 'z',

1);

insert into child_lzh_88 values(1003, 'c',

2);

insert into child_lzh_88 values(1004, 'd',

2);

commint;

SQL> select * from child_lzh_88;

ID C_NAME

P_ID

----------

-------------------- ----------

1001 l

1

1002 z

1

1003 c

2

1004 d

2

SQL> select * from parent_lzh_88;

ID NAME

----------

-------------------------

1 Admin

2 test

SQL> delete from parent_lzh_88 where

id=1;

1 row

deleted.

SQL> select * from parent_lzh_88;

ID NAME

----------

-------------------------

2 test

SQL> select * from child_lzh_88;

ID C_NAME

P_ID

-------

-------------------- ----------

1003 c  2

1004 d  2