PostgreSQL:如何限制一个表的多个字段不能同时为空?

今天有开发组的同事咨询数据库是否有方法可以限制一个表中的多个字段不能同时为空,起始想到的是创建 trigger 的方法,但 trigger 比较费性能,不建议使用,后来想到可以通过创建约束轻松实现,如下:

创建测试表

1
2
3
4
5
6
[pg93@redhatB pg_root]$ psql francs francs
psql (9.3beta1)
Type "help" for help.

francs=> create table test_con (col_a character varying(32),col_b character varying(32),col_c character varying(32));
CREATE TABLE

创建约束

1
2
3
4
5
6
7
8
9
10
11
12
francs=> alter table test_con add constraint con_1 check (col_a is not null or col_b is not null or col_c is not null);
ALTER TABLE

francs=> \d test_con
Table "francs.test_con"
Column | Type | Modifiers
--------+-----------------------+-----------
col_a | character varying(32) |
col_b | character varying(32) |
col_c | character varying(32) |
Check constraints:
"con_1" CHECK (col_a IS NOT NULL OR col_b IS NOT NULL OR col_c IS NOT NULL)

备注:三个字段不能同时为空。

插入测试数据

插入测试数据验证约束,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
francs=> insert into test_con (col_a,col_b,col_c) values ('a','a','a');
INSERT 0 1
francs=> insert into test_con (col_a,col_b,col_c) values ('a',null,null);
INSERT 0 1
francs=> insert into test_con (col_a,col_b,col_c) values (null,'a',null);
INSERT 0 1
francs=> insert into test_con (col_a,col_b,col_c) values (null,null,'a');
INSERT 0 1

francs=> select * from test_con;
col_a | col_b | col_c
-------+-------+-------
a | a | a
a | |
| a |
| | a
(4 rows)

francs=> insert into test_con (col_a,col_b,col_c) values (null,null,null);
ERROR: new row for relation "test_con" violates check constraint "con_1"
DETAIL: Failing row contains (null, null, null).

备注:创建约束能轻松满足需求,当然也可以创建触发器实现此功能,这里不演示了。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%