约束

原创 JGJ 2023-09-26

119

约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

约束确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

约束对于捕获异常值和应用程序代码没有考虑到，但需要在insert语句之前捕获的内容也是非常有用的。

为了说明主要的约束类型，将以下图所展示一个示例数据库模式，当您正在创建一个房间预订系统，其中包含一个用户表、一个房间表，以及引用用户和房间的预订表以及一个开始和结束时间。

在没有任何限制的情况下为用户和房间设置前两个表：

CREATE TABLE users (         id serial PRIMARY KEY,            name text,        email text     ); 
CREATE TABLE rooms (         id serial PRIMARY KEY,        roomnumber text    );

主键约束：PRIMARY KEY

主键是一行数据的唯一标识，要求非空且唯一，一张表只能有一个主键。

-- 1.添加约束

-- 1.1创建表时添加主键约束

如上面创建Users及Rooms表

-- 1.2建完表后添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

-- 2.删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

外键约束：FOREIGN KEY‍

添加引用这些主键的第三个表，将使用外键约束。

 CREATE TABLE reservations (        user_id int references users(id),        room_id int references rooms(id),        Start_time timestamp,        end_time timestamp,        event_title text      );

可以将预订表绑定到其他数据表，确保它们与主键绑定在一起。

还可以通过以下方式在现有表上创建外键约束ALTER TABLE：

ALTER TABLE public.reservations       ADD CONSTRAINT reservations_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
ALTER TABLE public.reservations       ADD CONSTRAINT reservations_room_id_fkey FOREIGN KEY (room_id) REFERENCES public.rooms(id);

一个外键约束被命名，例如reservations_user_id_fkey。如果不提供名字，会自动生成一个。

级联和外键

使用外键约束时，应该要注意到级联更新和删除语句。

外键可以定义对链接表进行更改时对数据的影响。这些是外键约束的修饰符ON DELETE。ON UPDATE如果您因 GDPR 或其他隐私要求而需要删除用户数据，则级联删除尤其重要。

例如，假设在这上面模式中，想要在一定时间后删除用户，并且也想要删除预订历史。如果删除用户行，添加此约束将删除保留表中的行。

ALTER TABLE public.reservations      ADD CONSTRAINT reservations_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;

如果您不提供ON DELETE CASCADE，数据库将阻止删除 users 表中的记录，除非首先删除该用户的所有预订记录。

注意：必须在添加外键约束时添加级联语句。事后无法通过ALTER TABLE。

唯一约束：UNIQUE

唯一约束是要求某一列或某一行中的数据是唯一的设置。这在创建用户名、唯一身份或任何主键时特别有用。

例如，我们希望对房间号设置唯一的约束，这样您就不会意外地得到重复的房间号：

ALTER TABLE ONLY public.rooms    ADD CONSTRAINT room_number_unique UNIQUE (roomnumber);

命名一个唯一的键约束，例如room_number_unique。如果你不提供名字，会自动生成一个。

非空约束：NOT NULL

在检查此数据模式时，需要将注意哪些地方不需要空数据。添加非空约束是一种很好的方法，可以确保不会添加不完整的数据行。

这里的一个例子是确保你有一个房间号来预订。

ALTER TABLE public.reservations ALTER COLUMN room_id SET NOT NULL;

另一个是确保所有预订都有开始和结束时间：

ALTER TABLE public.reservations ALTER COLUMN start_time SET NOT NULL;ALTER TABLE public.reservations ALTER COLUMN end_time SET NOT NULL;

如果查询约束列表，非空约束没有命名并且不会出现在 pg_constraints系统表中。

检查约束：CHECK

通过让数据库在插入之前检查某些内容，检查约束是向数据添加一些简单逻辑的好方法。检查约束将应用于表中的单行。

例如，在这个模式中，需要为预订时间添加一些逻辑。开始时间应小于结束时间。开始时间应大于上午 8 点且小于下午 5 点。并且开始时间和结束时间的间隔大于30分钟。

检查约束的语法：

start_time 小于结束时间

ALTER TABLE public.reservations       ADD CONSTRAINT start_before_end check (start_time < end_time );

开始时间必须大于上午 8 点，结束时间必须小于下午 5 点。

ALTER TABLE public.reservations       ADD CONSTRAINT daytime_check check (start_time::time >= '08:00:00' AND end_time::time <= '17:00:00');

开始时间和结束时间的间隔大于 30 分钟。

ALTER TABLE public.reservations        ADD CONSTRAINT interval_check check (end_time - start_time >= interval '30 minutes');

查找数据库中的约束

如果需要查看已经拥有的约束，下面的查询，它将显示出目前已经创建过的所有类型的约束：

SELECT * FROM (        SELECT                   c.connamespace::regnamespace::text as table_schema,             c.conrelid::regclass::text as table_name,             con.column_name,             c.conname as constraint_name,            pg_get_constraintdef(c.oid)    FROM             pg_constraint c    JOIN            pg_namespace ON pg_namespace.oid = c.connamespace      JOIN            pg_class ON c.conrelid = pg_class.oid    LEFT JOIN        information_schema.constraint_column_usage con ON          c.conname = con.constraint_name AND        pg_namespace.nspname = con.constraint_schema    UNION ALL    SELECT        table_schema, table_name, column_name, NULL, 'NOT NULL'    FROM information_schema.columns    WHERE            is_nullable = 'NO'         ) all_constraints WHERE     table_schema NOT IN ('pg_catalog', 'information_schema')     ORDER BY table_schema, table_name, column_name, constraint_name ;

数据库国产数据库集中式数据库分布式数据库

最后修改时间：2024-01-26 11:21:43

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

约束

评论