DOMAIN域对象的使用介绍

多米爸比@云和恩墨

于 2024-04-07 08:09:58 发布

阅读量39

点赞数

文章标签：数据库 postgresql

原文链接：https://www.modb.pro/db/627727

版权

在PostgreSQL数据库中，domain域对象有如下用途：

作为别名支持兼容性数据类型

创建自定义的数据类型binary_float，对不同数据库的数据类型进行兼容。

CREATE DOMAIN binary_float AS float;

为数据类型进行集中式的数据校验

很多需要进行相同限制的数据列，比如电话号码或邮箱格式必须满足一定规则，可以使用domain域的特性创建新类型。

domain域对象在schema级别定义一次后，可以控制多个字段，也可以被其他多个表使用，相比check约束也容易修改，便于集中式统一管理，它的好处显而易见。不过也需要注意如果修改或删除domain域，对所有关联表的影响。

下面对数据校验进行示例介绍：

创建domain域对象

创建domain域对象，对指定类型值进行check约束，确保出生日期都超过1949年10月1日，确保电子邮件地址有效，可以创建下面两个DOMAIN对象：

CREATE DOMAIN date_of_birth AS date CHECK (value > '1949-10-01'::date) ; CREATE DOMAIN valid_email AS text NOT NULL CHECK (value ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$') ;

在表定义中使用domain域对象

现在创建表时，可以使用date_of_birth和valid_email数据类型，例如人员的出生日期和邮件地址：

CREATE TABLE person_using_domains( id INTEGER GENERATED always AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, birth_date DATE_OF_BIRTH, email VALID_EMAIL );

在上面的SQL中，birth_date列使用DATE_OF_BIRTH域对象，email列使用VALID_EMAIL域对象。

domain域对象插入测试

现在我们可以插入行，检查birth_date列是否遵守域对象的规则：

postgres=# INSERT INTO person_using_domains(name,birth_date,email) VALUES ('test','1950-01-01', 'test@domain.com'); INSERT 0 1

上面的语句执行成功，下面的语句执行会失败：

postgres=# INSERT INTO person_using_domains(name,birth_date,email) VALUES ('test1','1900-01-01', 'test1@domain.com'); ERROR: value for domain date_of_birth violates check constraint "date_of_birth_check"

因为域对象的check检查失败。

可以看到域对象符合预期，现在也可以在其他表定义中使用它。

domain域对象设置默认值

域对象的默认值也需要满足约定的规则，例如下面

ALTER DOMAIN date_of_birth SET DEFAULT '1920-01-01'::date;

使用下面的语句插入，会使用域对象的默认值。

postgres=# INSERT INTO person_using_domains(name,email) VALUES ('test2','test2@domain.com'); ERROR: value for domain date_of_birth violates check constraint "date_of_birth_check"

此时会得到上面的错误，该错误是由于我们的默认值1920-01-01不符合date_of_birth域对象中定义的约束。如果我们将默认值修改为1980-01-01，则可以正常使用域对象的默认值。

ALTER DOMAIN date_of_birth SET DEFAULT '1950-01-01'::date;

注意默认值有一个层次结构：数据类型默认值 -> 域对象默认值 -> 表列默认值。数据类型默认值可以被域对象默认值覆盖，域对象默认值可以被表列默认值覆盖。

修改domain域对象

假如业务发生变更，现在出生日期只想存储1980年之后出生的人，可以使用ALTER DOMAIN进行修改：

ALTER DOMAIN date_of_birth ADD CHECK (value > '1980-01-01'::date);

注意域对象的默认值也需要同步进行修改。

已在使用的域对象，进行域对象修改时要注意。

首先我们删除旧的约束：

ALTER DOMAIN date_of_birth
    DROP CONSTRAINT date_of_birth_check;

然后添加新的check规则：

ALTER DOMAIN date_of_birth ADD CHECK (value > '1980-01-01'::date);

但是上面的命令会产生错误：

ERROR:  column "birth_date" of table "person_using_domains" contains values that violate the new constraint

有不满足新约束的数据：

postgres=# SELECT *
           FROM person_using_domains
           WHERE birth_date <= '1980-01-01'::date;
┌────┬──────┬────────────┬─────────────────┐
│ id │ name │ birth_date │      email      │
├────┼──────┼────────────┼─────────────────┤
│  3 │ test │ 1950-01-01 │ test@domain.com │
└────┴──────┴────────────┴─────────────────┘
(1 row)

我们可以先使用NOT VALID定义约束：

ALTER DOMAIN date_of_birth 
    ADD CONSTRAINT date_of_birth_new_check CHECK (value > '1980-01-01'::date)
    NOT VALID;

然后需要更新表中不符合新约束的数据：

UPDATE person_using_domains SET birth_date = '1981-01-01'::date where birth_date <= '1980-01-01'::date;

修改完数据后，再执行如下语句让约束生效：

ALTER DOMAIN date_of_birth
    VALIDATE CONSTRAINT date_of_birth_new_check;

现在date_of_birth域对象的约束检查已启用，下面的语句可以看到新的约束已生效。

postgres=# INSERT INTO person_using_domains(name,birth_date,email) VALUES ('test3','1960-01-01', 'test3@domain.com'); ERROR: value for domain date_of_birth violates check constraint "date_of_birth_new_check"