主页 / user-80273

Matjaž's questions

Martin Hope
Matjaž
Asked: 2017-03-07 01:55:07 +0800 CST
  • 4

在 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