主页 / dba / 问题 / 166308
Accepted
Matjaž
Asked: 2017-03-07 01:55:07 +0800 CST

PostgreSQL 范围边界上的外键

  • 772

在 PostgreSQL 9.6 中是否可以在范围的下限和上限（在我的例子中是整数）上应用外键约束？

目前我将下限和上限保留在两列中，但想将它们统一到一个范围列中，同时保留外键引用。

例子

我有一个包含会话列表的表，例如：

CREATE TABLE sessions (
    session_id    SERIAL      PRIMARY KEY,
    session_start TIMESTAMPTZ NOT NULL,
    description   TEXT
);

然后是这些会议的分组表，目前表示为：

CREATE TABLE session_groups (
    group_id      SERIAL  PRIMARY KEY,
    first_session INTEGER NOT NULL UNIQUE,
    last_session  INTEGER NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (first_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (last_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,

    CONSTRAINT last_session_after_first
         CHECK (last_session >= first_session)
);

我想要什么：

CREATE TABLE session_groups (
    group_id      SERIAL    PRIMARY KEY,
    session_range INT4RANGE NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (lower(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (upper(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT

    -- Dropped last_session_after_first constraint since
    -- the INT4RANGE type checks it automatically.
);

这是抛出语法错误。

postgresql foreign-key

1 个回答

  1. Best Answer

    FOREIGN KEY约束中不允许有任意表达式，只能是列。这就是为什么在尝试第二种方法时会出现错误的原因。

    但是，您可以使用 aVIEW访问表：

    CREATE VIEW v_session_groups 
    (group_id, session_range, description) AS
SELECT
    group_id,
    int4range(first_session, last_session, '[)'),
    description
FROM 
    session_groups ;

    您甚至可以添加触发器，然后能够从视图中插入/更新/删除，就好像它是一个普通表一样。

    旁注：

    • 第一个设计有 2 个UNIQUE约束，在(first_session)和上(last_session)，而第二个设计有 1 个UNIQUE约束，在 上(first_session, last_session)。这些不是等效设计，因此请重新检查您的要求。
    • 我为该列使用了默认'[)'（包含-排除）参数int4range。您可能希望将其更改为'[]'（全包），具体取决于您希望如何保存范围。
      示例：int4range(1,1)andint4range(1,1,'[)')是空范围，而int4range(1,1,'[]')是[1,2)范围。

    • 如果你想强制/避免重叠范围，那么你根本不需要UNIQUE约束，只需要一个EXCLUDE约束：

      CREATE TABLE session_groups (
    group_id      SERIAL  PRIMARY KEY,
    first_session INTEGER NOT NULL,
    last_session  INTEGER NOT NULL,
    description   TEXT,

    FOREIGN KEY (first_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (last_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,

    CONSTRAINT non_overlapping_sessions
        EXCLUDE  USING gist
        ( int4range(first_session, last_session, '[)') WITH && )
);

      另请参阅有关范围类型约束的文档。您可能还需要添加扩展名：

      CREATE EXTENSION btree_gist;
    • 7

相关问题