文档章节

PostgreSQL foreign key

廖君
 廖君
发布于 2015/01/25 18:22
字数 1236
阅读 52
收藏 0

什么是外键?

外键就是用来关联表的节点,是为了保持表的参照完整性。一个表中的外键必须能够在另外一个关系表中能够匹配到.

官方文档中的例子为例:

假如现在有一个订单表,需要确保订单order里面的商品在product里面存在,这里我们就需要在order增加一个外键这个外键就是product表里面的主键

create table product(
	product_no  serial constraint product_pkey primary key  ,
	name text,
	others character varying(100)
);

上面这个例子创建了一个product表,主键是product_no 主键的名字是product_pkey

 下面创建一个orders表,外键是product_no,参照product表中的product_no

create table orders(
    order_no serial primary key,
    product_no integer references product (product_no) ,
    name text,
    others character varying(100)
);

当创建之后,如果你在order表里面插入一条数据,而所插入的product_no的在product表里面没没有就会出现报错

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_product_no_fkey"
DETAIL:  Key (product_no)=(1) is not present in table "product".


回到这里

create table orders(
	order_no serial primary key,
	product_no integer ,
	name integer references product,
	others character varying(100)
);

当我们执行上面一句的时候也是可以通过的,即使外键约束的字段和被引用表的字段不同,只要与参照表主键的数据类型一样就可以了。但是这里并不是很推荐,不方便以后的维护。

在上面这个例子里面提到了一种不同的写法:

create table orders(
    order_no serial primary key,
    product_no integer  references product,
    name text ,
    others character varying(100)
);

上面这种写法,当缺少字段列表的时候,系统会自动引用被引用表的主键作为order表的外键

但是它不能像创建主键一样写成下面这种方式:

create table orders(
    order_no serial primary key,
    product_no integer foreign key,
    name integer,
    others character varying(100)
);

这样会报错

外键也能够像检查约束一样,把一个字段组创建为一个外键,例如:

create table t1(
    a integer primary key,
    b integer,
    c integer,
    FOREIGN KEY (b, c)  REFERENCES t2(b,c)
);

不过执行上面这条SQL之前你需要先执行下面这条,不然没有引用表,说t2不存在:

create table t2(
	a integer primary key,
	b integer,
	c integer,
	unique (b,c)
);

执行完上面这条语句之后就会创建一个组外键。说到这儿可以发现外键不一定是被引用表中的主键,只要外键的字段在被引用表的字段拥有唯一约束就可以。

一个表里面可以创建多个外键,这种实现的原因是为了实现多对多的一种关系,假如你的订单表order有多个商品:

-----------product table
create table product(
	product_no serial    primary key,
	name text
);
-----------orders table
create table orders(
	order_no serial primary key,
	shipping text
);
------------relationship table
create table order_item(
	order_no integer references orders,
	product_no integer references product,
	detail text,
	primary key (product_no,order_no)
);

最后的关系表order_item:外键和主键是重叠的。在标准里面是允许的。

在生产环境中经常有这样的情况:当创建了某个上面的订单,而在未来的某一刻,商品表中的某个商品被删除了呢?

上面那种情况是不能删除的,你只有把order_item表里面在product有关系的数据删除才能够删除product表中的数据,但是想删除:

-----------product table
create table product(
	product_no serial    primary key,
	name text
);
-----------orders table
create table orders(
	order_no serial primary key,
	shipping text
);
------------relationship table
create table order_item(
	order_no integer references orders on delete restrict,
	product_no integer references product on delete cascade,
	detail text,
	primary key (product_no,order_no)
);

上面的这段语句,restrict与cascade分别是限制级联

限制的意思是当在关联表里面存在被引用表的值,则不允许删除;

级联的意思是当关联表里面存在被引用表的值,假如删除被引用表的值,那么关联表中的数据也会随之删除

系统默认是restrict

除了上面两个常用的选项之外,为了业务逻辑的考虑有的为了保留数据。还有set null与set default。set default必须保证默认值能够拥有关联性,能够起到外键的效果。不然就会抛出错误,上面的例子中就不可以设置为default,但是你可以设置为null,但是设置为null也会出现问题,就是被引用表的字段数据也是NULL,如果这样就会造成数据混乱的问题。在这里建议在设置关联字段的时候,把它设置为not null。

与ON delete与之对应的还有ON UPDATE。数据更新也是一样的。

对于删除与更新操作,都会扫描关联表的整个表。例如上面的那个例子,当删除product表中的某一条数据的时候,它会扫描整个关联表order_item。所以在这里你需要创建一个索引。以便能够快速扫描。

© 著作权归作者所有

上一篇: PostgreSQL修改表
廖君
粉丝 13
博文 71
码字总数 35592
作品 0
深圳
私信 提问
PostgreSQL的外键深入使用

有开发同事问及postgresql外键的用法,这里普及一下。外键是一个很基础的概念,使用得当可以对事务的一致性有很好的保障,方法上和Oracle是很接近的,作用很简单地说就是保证子表的数据都能在...

kenyon_君羊
2013/06/01
0
4
PostgreSQL Oracle 兼容性之 - ALTER TRIGGER ENABLE|DISABLE

标签 PostgreSQL , 触发器 , enable , disable 背景 Oracle 临时关闭或启用触发器的语法如下 https://docs.oracle.com/cd/B1930601/server.102/b14200/statements4001.htm PostgreSQL 触发器......

德哥
2018/05/06
0
0
PostgreSQL 11 preview - 分区表 增强 汇总

标签 PostgreSQL , 分区表 , 增强 , 11 背景 PostgreSQL 11 分区表有非常多的增强特性。 E.1.3.1.1. Partitioning Allow faster partition elimination during query processing (Amit Lango......

德哥
2018/07/28
0
0
ora2pg 11.4 发布,Oracle 转 PostgreSQL 工具

ora2pg 11.4 修复很多跟移植消耗估计相关的主要问题,请立即更新。 Ora2pg可以用来将Oracle数据库转换成PostgreSQL,主要功能: - Export full database schema (tables, views, sequences,...

oschina
2013/05/29
362
0
PostgreSQL 9.3 Beta 2 发布

PostgreSQL 9.3 发布第二个 Beta 版本,与 Beta1 比较,该版本主要是 bug 的修复,包括: prevent xid gaps in Hot Standby startup prevent insertion deadlocks with SP-GiST indexes add......

oschina
2013/06/28
1K
5

没有更多内容

加载失败,请刷新页面

加载更多

非递归实现后序遍历二叉树

问题描述 从键盘接受输入先序序列,以二叉链表作为存储结构,建立二叉树(以先序来建立)并对其进行后序遍历,然后将遍历结果打印输出。要求采用非递归方法实现。 解题思路 Push根结点到第一...

niithub
19分钟前
2
0
IDEA 自动删除类中无用的import包

1.手动快捷键 Ctrl + Alt + O 2.配置自动清理 Ctrl + Alt + S 打开设置 然后,进行如下操作:

时刻在奔跑
21分钟前
2
0
cwRsync提示password file must be owned by root when running as root的解决方法

cwRsync提示password file must be owned by root when running as root的解决方法 更新时间:2015年08月30日 23:05:52 投稿:mdxy-dxy 我要评论 今天在配置服务器的时候,用了rsync4.10版本...

rootliu
26分钟前
1
0
Java 交换两个参数

目前有一个小需求: 定义一个函数,交换两个值。于是,有人就写了这样一个函数。 public class exchange_01 { public static void main(String[] args) { // TODO Auto-gen...

hellation_
35分钟前
1
0
ExoPlayer简单使用

一、介绍 ExoPlayer是google开源的应用级媒体播放器项目,该开源项目包含ExoPlayer库和演示demo,github地址:https://github.com/google/ExoPlayer。 二、概述 ExoPlayer库的核心是ExoPlay...

零点B5
44分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部