约束与触发器

完整性约束

删除/更新具有引用关系的表时,有以下操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 被引用的行禁止删除
on delete restrict

-- 级联,被引用行删除时,引用行也一起删除;即子表里的行跟着父表里相应的行一起删除
on delete cascade

-- 被引用行删除时,引用行不做什么处理
no action

-- 被引用行禁止更新
on update restrict

-- 被引用行更新时,引用行自动更新
on update cascade


create table so_items(
item_id int not null,
so_id int references so_headers(id) on delete restrict,
product_id int,
net_price numeric,
primary key(item_id,so_id)
);

create table so_items(
item_id int not null,
so_id int references so_headers(id) on delete cascade,
product_id int,
net_price numeric,
primary key(item_id,so_id)
);

外键约束的几种方法:

方法1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table so_headers(
id serial primary key,
customer_id int,
ship_to varchar(255)
);

-- 在创建表时,作为外键的列后面直接依赖父表列
create table so_items(
item_id int not null,
so_id int references so_headers(id),
product_id int,
net_price numeric,
primary key(item_id,so_id)
);

方法2:

1
2
3
4
5
6
7
8
9
10
-- 创建表时,在末尾指定外键约束
create table so_items(
item_id int not null,
so_id int,
product_id int,
net_price numeric,
primary key(item_id,so_id),
foreign key(so_id) references so_headers(id)
);
-- 【注】:如果没有指定外键别名,则默认的外键别名为:"表名_列名_fkey"

方法3:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 创建表时,在末尾指定外键约束,并且指定外键别名;so_id_fkey
create table so_items(
item_id int not null,
so_id int,
product_id int,
net_price numeric,
primary key(item_id,so_id),
constraint so_id_fkey foreign key(so_id) references so_headers(id)
);


-- 多个主键建立外键约束
create table child_table(
c1 int primady key,
c2 int,
c3 int,
primary key(c2,c3) references 父表(p1,p2)
);

方法4:

1
2
3
4
5
6
-- 修改表添加外键约束
alter table 表名 add constraint 外键别名 foreign key(列名) references 父表(列名);


-- 删除外键约束
alter table 表名 drop constraint 外键别名;

DEFERRABLE

推迟约束

触发器

触发器(trigger)是用户定义在关系表上的由事件驱动调用函数的机制。

触发器比CHECK更灵活,可以实施各种复杂的检查和操作,具有更精细和更强大的数据保护能力。

在创建触发器之前,必须首先创建触发器函数,触发器函数的语法格式是:

1
2
3
4
5
6
CREATE FUNCTION function_name() RETURNS TRIGGER AS $$
DECLARE 变量声明;
BEGIN
函数执行代码
END;
$$ LANGUAGE plpgsql;

触发器中有两对前是函数头部。触发器函数定义的头部RETURNS后面只能是TRIGGER,并且触发器函数不能带任何参数。

两对$$之间是函数体。包括DECLARE部分的变量声明以及BEGIN和END之间的函数执行代码。DECLARE部分是可选的。

由于PG允许使用各种语言比如PL/pgSQL,C,Python来编写函数,所以第二对$$之后是对函数编写语言的说明。这里是PL/pgSQL。

触发器函数创建后,使用CREATE TRIGGER命令创建触发器。

1
2
3
4
5
CREATE TRIGGER name {BEFORE|AFTER} {event [OR...]}
ON TABLE YYY
[FOR [EACH] {ROW|STATEMENT}]
[WHEN {condition}]
EXECUTE PROCEDURE function_name();

{event [OR…]}中,{}里面是一个或多个用OR分隔的事件列表。这里的事件包括数据库的数据修改操作,比如INSERT、DELETE或UPDATE等命令。

BEFORE|AFTER的意思是触发器可以分为BEFORE和AFTER触发器,分别在操作完成前和操作完成后执行触发器函数。

ON TABLE后面给出触发器所在表的表名。

触发器可以按行或按语句触发,也就是行级触发器和语句级触发器。

行级:[FOR [EACH] {ROW|STATEMENT}]

语句级:[FOR [EACH] {STATEMENT}]

行级触发器的触发器函数为触发语句影响的每一行执行一次。

语句级触发器的触发器函数为每条触发语句执行一次。

假设表examiner有10000行,定义了如下的UPDATE触发器:

1
UPDATE examiner SET erage = erage + 1;

如果是语句级触发器,则执行完该语句后,触发动作只发生1次;如果是行级触发器,则执行10000次。

触发器必须返回一个NULL或者一个元组类型的变量。

语句级触发器应返回NULL。

行级after触发器的值总是被忽略,可以返回null。

行级before触发器的返回值不同,对触发器操作的影响也不同。

如果返回NULL则忽略该触发器的行级操作,其后的触发器也不会执行。

如果返回非NULL,则返回的行将成为被插入或更新的行。

如果是行级触发器,可以在触发器函数中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/DELETE事件之前的旧值。

插入examinee表的考号长度必须为10位:

创建触发器函数:

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION examineeid() RETURNS TRIGGER AS $examineeid$
BEGIN
IF(CHAR_LENGTH(new.eeid)<>10) THEN
RAISE EXCEPTION '格式错误';
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END
$examinee$ LANGUAGE plpgsql;

NEW代表INSERT或UPDATE操作产生的新的数据行

创建触发器:

1
2
CREATE TRIGGER examineeid_insert BEFORE INSERT ON examinee
FOR EACH ROW EXECUTE PROCEDURE examineeid();

三种:

1.DEFERRABLE INITIALLY DEFERRED

2.DEFERRABLE INITIALLY IMMEDIATE

3.NOT DEFERRABLE

1
Copy"subject_iddddd" INTEGER REFERENCES "Subjects" ("id") DEFERRABLE INITIALLY IMMEDIATE

注1:IMMEDIATE 会在每一个语句执行后进行约束检查,DEFERRED 则只会在事务结束时才检查约束。(DEFERRED 只是推迟检查而不是不检查)

注2:此设置仅影响 UNIQUE,PRIMARY KEY,REFERENCES (外键)和 EXCLUDE 约束

1
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

注1:NOT DEFERRABLE 来说,SET CONSTRAINTS 不生效。

注2:SET CONSTRAINTS ALL 更改所有 DEFERRABLE 约束。

Author

preccrep

Posted on

2021-05-10

Updated on

2021-05-11

Licensed under

You need to set install_url to use ShareThis. Please set it in _config.yml.
You forgot to set the business or currency_code for Paypal. Please set it in _config.yml.

Comments

You forgot to set the shortname for Disqus. Please set it in _config.yml.
You need to set client_id and slot_id to show this AD unit. Please set it in _config.yml.