约束

Halo Tech 2022-07-15

126

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

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

约束可以在创建表时规定（通过 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 ;

table 外键

文章转载自Halo Tech，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

约束

评论