1. 云图网首页
  2. 技术专区
  3. 数据库

postgresql—-数据库表约束—-FOREIGN KEY详解数据库

• 数据库

六、FOREIGN KEY —- 外键约束

外键可以是单个字段，也可以是多个字段。所谓的外键约束就是引用字段必须在被引用字段中存在，除非引用字段部分为NULL或全部为NULL（由MATCH TYPE决定），否则INSERT或UPDATE时将返回失败，且被引用字段必须有唯一约束或是主键。

外键约束语法相对较复杂一点，创建外键的语法如下：

ALTER TABLE tbl_foreign CONSTRAINT fk_constraint FOREIGN KEY（col1,col2） REFERENCES tbl_foreign_refd(refd_col1,refd_col2) MATCH [SIMPLE|FULL] ON DELETE [CASCADE|NO ACTION] ON UPDATE [CASCADE|NO ACTION];

其中：

tbl_foreign             : 引用表

fk_constraint           : 外键约束名称

(col1,col2)             : 引用表中引用字段

tbl_foreign_refd        : 被引用表

(refd_col1,refd_col2)   : 被引用表中被引用字段，和(col1,col2)对应

MATCH [SIMPLE|FULL]     : 外键匹配模式,如果引用字段全部不是NULL,则强匹配,否则根据匹配模式进行弱匹配。

–SIMPLE,默认值，只要引用字段中任一字段为NULL，则不要求与被引用字段强匹配；

–FULL,只有引用字段全部为NULL，才不要求与被引用字段强匹配。

 

ON DELETE [CASCADE | NO ACTION] : 默认NO ACTION

–CASCADE,删除被引用表数据级联删除引用表数据

–NO ACTION,删除被引用表数据必须先删除引用表数据，否则，如果引用表如果存在数据，直接删除被引用表数据返回失败。

 

ON UPDATE [CASCADE | NO ACTION] : 默认NO ACTION

–CASCADE,更新被引用表时级联更新引用表数据

–NO ACTION,更新被引用表时必须先删除引用表数据，否则，如果引用表存在数据，直接更新被引用表数据返回失败。

 

1.创建测试表(引用表tbl_foreign和被引用表tbl_foreign_refd)

create table tbl_foreign_refd( 
a int not null, 
b int not null, 
c varchar 
); 
alter table tbl_foreign_refd add constraint pk_tbl_foreign_refd_a_b primary key(a,b); 
 
create table tbl_foreign( 
a int, 
b int, 
c varchar 
); 
alter table tbl_foreign add constraint fk_tbl_foreign_a_b foreign key(a,b) references tbl_foreign_refd(a,b);

上表中完整外键其实如下，因为match,on delete,on update会自动使用默认值。

test=# alter table tbl_foreign add constraint fk_tbl_foreign_a_b foreign key(a,b) references tbl_foreign_refd(a,b) match simple on delete no action on update no action;

 

测试例1.match simple on delete no action on update no action

test=# insert into tbl_foreign_refd (a,b) values (1,1),(1,2),(1,3); 
INSERT 0 3 
test=# insert into tbl_foreign(a,b) values (1,1),(1,2); 
INSERT 0 2 
 
test=# insert into tbl_foreign(a,b) values (2,1); 
ERROR:  insert or update on table "tbl_foreign" violates foreign key constraint "fk_tbl_foreign_a_b" 
DETAIL:  Key (a, b)=(2, 1) is not present in table "tbl_foreign_refd". 
 
test=# insert into tbl_foreign(a) values (2); 
INSERT 0 1 
test=# insert into tbl_foreign(a) values (1); 
INSERT 0 1 
test=# select * from tbl_foreign; 
 a |  b   |  c    
---+------+------ 
 1 |    1 | NULL 
 1 |    2 | NULL 
 2 | NULL | NULL 
 1 | NULL | NULL 
(4 rows) 
test=# delete from tbl_foreign_refd where a=1 and b=1; 
ERROR:  update or delete on table "tbl_foreign_refd" violates foreign key constraint "fk_tbl_foreign_a_b" on table "tbl_foreign" 
DETAIL:  Key (a, b)=(1, 1) is still referenced from table "tbl_foreign". 
 
 
test=# update tbl_foreign_refd set a=3 where a=1 and b=1; 
ERROR:  update or delete on table "tbl_foreign_refd" violates foreign key constraint "fk_tbl_foreign_a_b" on table "tbl_foreign" 
DETAIL:  Key (a, b)=(1, 1) is still referenced from table "tbl_foreign".

 

测试例2.match full on delete cascade on update cascade

删除外键约束，清空数据，重新增加外键

test=# alter table tbl_foreign drop constraint fk_tbl_foreign_a_b ; 
ALTER TABLE 
test=# delete from tbl_foreign; 
DELETE 4 
test=# alter table tbl_foreign add constraint fk_tbl_foreign_a_b foreign key(a,b) references tbl_foreign_refd(a,b) match full on delete cascade on update cascade; 
ALTER TABLE

 

test=# insert into tbl_foreign(a,b) values (1,1),(1,2); 
INSERT 0 2 
 
test=# insert into tbl_foreign(a) values (2); 
ERROR:  insert or update on table "tbl_foreign" violates foreign key constraint "fk_tbl_foreign_a_b" 
DETAIL:  MATCH FULL does not allow mixing of null and nonnull key values. 
 
test=# insert into tbl_foreign(c) values (2); 
INSERT 0 1 
test=# select * from tbl_foreign; 
  a   |  b   |  c    
------+------+------ 
    1 |    1 | NULL 
    1 |    2 | NULL 
 NULL | NULL | 2 
(3 rows) 
 
test=# delete from tbl_foreign_refd where a=1 and b=1; 
DELETE 1 
test=# update tbl_foreign_refd set a=2 where a=1 and b=2; 
UPDATE 1 
test=# select * from tbl_foreign; 
  a   |  b   |  c    
------+------+------ 
 NULL | NULL | 2 
    2 |    2 | NULL 
(2 rows) 
 
test=# update tbl_foreign set a=3 where a=2; 
ERROR:  insert or update on table "tbl_foreign" violates foreign key constraint "fk_tbl_foreign_a_b" 
DETAIL:  Key (a, b)=(3, 2) is not present in table "tbl_foreign_refd".

 

2.删除外键约束

alter table tbl_foreign drop constraint fk_tbl_foreign_a_b ;

 

3.增加外键约束

和唯一键，主键一样，增加外键约束前首先要删除脏数据，对外键来说脏数据针对不同的match type来说是不一样的。

match simple : 引用字段全部是NOT NULL，且在被引用表中不存在的。

match full   : 引用字段部分是NULL和全部是NOT NULL且在被引用表中不存在的。

 

情况一：增加match simple类型外键

第一步：删除外键，清空表，写入测试数据

test=# alter table tbl_foreign drop constraint fk_tbl_foreign_a_b ; 
ALTER TABLE 
test=# delete from tbl_foreign; 
DELETE 2 
test=# insert into tbl_foreign(a,b) values (1,2),(2,2),(1,1); 
INSERT 0 3 
test=# insert into tbl_foreign(a) values (3),(4); 
INSERT 0 2 
test=# insert into tbl_foreign(c) values (5); 
INSERT 0 1 
test=# select * from tbl_foreign; 
  a   |  b   |  c    
------+------+------ 
    1 |    2 | NULL 
    2 |    2 | NULL 
    1 |    1 | NULL 
    3 | NULL | NULL 
    4 | NULL | NULL 
 NULL | NULL | 5 
(6 rows) 
 
test=# select * from tbl_foreign_refd ; 
 a | b |  c    
---+---+------ 
 1 | 3 | NULL 
 2 | 2 | NULL 
(2 rows)

对于要增加的外键来说，(1,1,NULL),(1,2,NULL)是脏数据。

第二步：查询脏数据

test=# select * from tbl_foreign where not exists (select null from tbl_foreign_refd where tbl_foreign_refd.a=tbl_foreign.a and tbl_foreign_refd.b=tbl_foreign.b) and a is not null and b is not null; 
 a | b |  c    
---+---+------ 
 1 | 1 | NULL 
 1 | 2 | NULL 
(2 rows)

第三步：删除脏数据

将上面SQL语句中的select替换成delete即可。

test=# delete from tbl_foreign where not exists (select null from tbl_foreign_refd where tbl_foreign_refd.a=tbl_foreign.a and tbl_foreign_refd.b=tbl_foreign.b) and a is not null and b is not null; 
DELETE 2

第四步：增加外键

test=# alter table tbl_foreign add constraint fk_tbl_foreign_a_b foreign key(a,b) references tbl_foreign_refd(a,b) match simple; 
ALTER TABLE

 

情况二：增加match full类型外键

第一步：删除外键，清空表，写入测试数据

test=# alter table tbl_foreign drop constraint fk_tbl_foreign_a_b ; 
ALTER TABLE 
test=# delete from tbl_foreign; 
DELETE 4 
test=# insert into tbl_foreign(a,b) values (1,2),(2,2),(1,1); 
INSERT 0 3 
test=# insert into tbl_foreign(a) values (3),(4); 
INSERT 0 2 
test=# insert into tbl_foreign(c) values (5); 
INSERT 0 1 
test=# select * from tbl_foreign; 
  a   |  b   |  c    
------+------+------ 
    1 |    2 | NULL 
    2 |    2 | NULL 
    1 |    1 | NULL 
    3 | NULL | NULL 
    4 | NULL | NULL 
 NULL | NULL | 5 
(6 rows) 
 
test=# select * from tbl_foreign_refd ; 
 a | b |  c    
---+---+------ 
 1 | 3 | NULL 
 2 | 2 | NULL 
(2 rows)

对于要增加的外键来说，(1,1,NULL),(1,2,NULL),(3,NULL,NULL),(4,NULL,NULL)是脏数据。

第二步：查询脏数据

test=# select * from tbl_foreign where not exists (select null from tbl_foreign_refd where tbl_foreign_refd.a=tbl_foreign.a and tbl_foreign_refd.b=tbl_foreign.b) and ((a is not null and b is not null) or (a is not null and b is null) or(a is null and b is not null)); 
 a |  b   |  c    
---+------+------ 
 1 |    1 | NULL 
 1 |    2 | NULL 
 3 | NULL | NULL 
 4 | NULL | NULL 
(4 rows)

第三步：删除脏数据

将上面SQL语句的SELECT替换成DELETE即可。

test=# delete from tbl_foreign where not exists (select null from tbl_foreign_refd where tbl_foreign_refd.a=tbl_foreign.a and tbl_foreign_refd.b=tbl_foreign.b) and ((a is not null and b is not null) or (a is not null and b is null) or(a is null and b is not null)); 
DELETE 4

第四步：增加外键约束

test=# alter table tbl_foreign add constraint fk_tbl_foreign_a_b foreign key(a,b) references tbl_foreign_refd(a,b) match full; 
ALTER TABLE

 

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/4883.html

赞 (0)
0
上一篇 2021年7月16日
下一篇 2021年7月16日

相关推荐

发表回复

登录后才能评论