Define multiple columns constraints

今天看到论坛上有篇贴子有点意思,这里记录下,问题是这样的,创建一张新表,并且需要对表上两个字段建立约束,约束的定义:columnA 或者 columnB 不能为空。以前在学习 oracle 时只能对某一张字段定义约束,今天才知道 PostgreSQL提供更强大的功能,可以对表上多个字段定义表级的约束,下面是测试步骤。

环境准备

创建表

1
2
3
4
5
skytf=> create table test_28 (  
skytf(> id integer,
skytf(> name varchar(32),
skytf(> address varchar(128));
CREATE TABLE

查看表结构

1
2
3
4
5
6
7
skytf=> /d test_28  
Table "skytf.test_28"
Column | Type | Modifiers
---------+------------------------+-----------
id | integer |
name | character varying(32) |
address | character varying(128) |

现在需要建立约束,约束定义:字段name或者 address 不能为空, 也就是这两个字段必须一个为非空。

创建约束

1
skytf=> alter table test_28 add constraint con_test_28 check ( name is not null or address is not null);ALTER TABLE

验证约束

1
2
3
4
5
6
7
8
9
skytf=> /d test_28  
Table "skytf.test_28"
Column | Type | Modifiers
---------+------------------------+-----------
id | integer |
name | character varying(32) |
address | character varying(128) |
Check constraints:
"con_test_28" CHECK (name IS NOT NULL OR address IS NOT NULL)

插入数据进行测试

1
2
3
4
5
6
skytf=> insert into test_28(id,name) values (1,'francs');  
INSERT 0 1
skytf=> insert into test_28(id,address) values (1,'HZ');
INSERT 0 1
skytf=> insert into test_28(id,name,address) values (1,null,null);
ERROR: new row for relation "test_28" violates check constraint "con_test_28"

备注:从上面测试步骤看出,约束正确,PostgreSQL真是功能强大啊。

文档解释

Column Check Constraints
The SQL standard says that CHECK column constraints can only refer to the column they apply to; only CHECK table constraints can refer to multiple columns. PostgreSQL does not enforce this restriction; it treats column and table check constraints alike.

Two ways to define constraints
There are two ways to define constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience for use when the constraint only affects one column.

原创文章,作者:bd101bd101,如若转载,请注明出处:https://blog.ytso.com/tech/database/236384.html

(0)
上一篇 2022年1月24日 21:28
下一篇 2022年1月24日 21:28

相关推荐

发表回复

登录后才能评论