今天看到论坛上有篇贴子有点意思，这里记录下，问题是这样的，创建一张新表，并且需要对表上两个字段建立约束，约束的定义：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.