Início / dba / Perguntas / 166308
Accepted
Matjaž
Asked: 2017-03-07 01:55:07 +0800 CST

Chave estrangeira nos limites de intervalo do PostgreSQL

  • 772

É possível no PostgreSQL 9.6 aplicar uma restrição de chave estrangeira nos limites inferior e superior de um intervalo (de inteiros no meu caso)?

Atualmente, mantenho o limite inferior e o limite superior em duas colunas, mas gostaria de unificá-los em uma coluna de intervalo, mantendo as referências de chave estrangeira.

Exemplo

Eu tenho uma tabela contendo uma lista de sessões, algo como:

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

e, em seguida, uma tabela de grupos dessas sessões que atualmente é expressa como:

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)
);

O que eu gostaria:

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.
);

que está lançando um erro de sintaxe.

postgresql foreign-key

1 respostas

  1. Best Answer

    Não é permitido ter expressões arbitrárias em FOREIGN KEYrestrições, apenas colunas. É por isso que você obtém um erro ao tentar a segunda abordagem.

    No entanto, você pode usar a VIEWpara acessar a tabela:

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

    Você pode até adicionar gatilhos e, em seguida, ser capaz de INSERT/UPDATE/DELETE da view, como se fosse uma tabela normal.

    Notas laterais:

    • O primeiro projeto tem 2 UNIQUErestrições, em (first_session)e (last_session), enquanto o 2º tem 1 UNIQUErestrição, em (first_session, last_session). Estes não são designs equivalentes, portanto, reexamine seus requisitos.
    • Eu usei o '[)'parâmetro padrão (inclusive-exclusivo) para a int4rangecoluna. Você pode querer mudar isso para '[]'(tudo incluído), dependendo de como você deseja salvar os intervalos.
      Exemplos: int4range(1,1)e int4range(1,1,'[)')é o intervalo vazio enquanto int4range(1,1,'[]')é o [1,2)intervalo.

    • Se você deseja impor / evitar intervalos sobrepostos, não precisa de uma UNIQUErestrição, mas de uma EXCLUDErestrição:

      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 && )
);

      Consulte também a documentação sobre restrições em tipos de intervalo . Você provavelmente precisará adicionar a extensão também:

      CREATE EXTENSION btree_gist;
    • 7

relate perguntas