约束

约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 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 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;

唯一约束：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

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

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 ;