Uma restrição de verificação é o tipo mais genérico de restrição. Permite especificar que os valores de uma determinada coluna devem corresponder a uma expressão booleana (valor-verdade). Por exemplo, para permitir apenas preços com valores positivos pode ser usado:

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric CHECK (preco > 0)
);

Como pode ser visto, a definição da restrição vem após o tipo de dado, assim como a definição do valor padrão. O valor padrão e a restrição podem estar em qualquer ordem. A restrição de verificação é formada pela palavra-chave CHECK seguida por uma expressão entre parênteses. A expressão da restrição de verificação deve incluir a coluna sendo restringida, senão não fará muito sentido.

Também pode ser atribuído um nome específico para a restrição. Isso torna mais clara a mensagem de erro, e permite fazer referência à restrição quando se desejar alterá-la. A sintaxe é:

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric CONSTRAINT preco_positivo CHECK (preco > 0)
);

Portanto, para especificar o nome da restrição deve ser utilizada a palavra-chave CONSTRAINT, seguida por um identificador, seguido por sua vez pela definição da restrição (Se o nome da restrição não for definido dessa maneira, o sistema escolherá um nome para a restrição).

Uma restrição de verificação também pode fazer referência a várias colunas. Supondo que serão armazenados o preço normal e o preço com desconto, e se deseje garantir que o preço com desconto seja inferior ao preço normal:

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric CHECK (preco > 0),
    preco_com_desconto numeric CHECK (preco_com_desconto > 0),
    CHECK (preco > preco_com_desconto)
);

As duas primeiras restrições já são familiares. A terceira utiliza uma nova sintaxe. Não está associada a uma coluna em particular, em vez disso aparece como um item à parte na lista de colunas separadas por vírgula. As definições das colunas e as definições dessas restrições podem estar em qualquer ordem.

Dizemos que as duas primeiras restrições são restrições de coluna, enquanto a terceira é uma restrição de tabela, porque está escrita separado das definições das colunas. As restrições de coluna também podem ser escritas como restrições de tabela, enquanto o contrário nem sempre é possível, porque supostamente a restrição de coluna somente faz referência à coluna onde está associada (O PostgreSQL não impõe esta regra, mas deve-se segui-la se for desejado que a definição da tabela funcione em outros sistemas de banco de dados). O exemplo acima também pode ser escrito do seguinte modo:

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric,
    CHECK (preco > 0),
    preco_com_desconto numeric,
    CHECK (preco_com_desconto > 0),
    CHECK (preco > preco_com_desconto)
);

ou mesmo:

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric CHECK (preco > 0),
    preco_com_desconto numeric,
    CHECK (preco_com_desconto > 0 AND preco > preco_com_desconto)
);

É uma questão de gosto.

Podem ser atribuídos nomes para as restrições de tabela da mesma maneira que para as restrições de coluna:

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric,
    CHECK (preco > 0),
    preco_com_desconto numeric,
    CHECK (preco_com_desconto > 0),
    CONSTRAINT desconto_valido CHECK (preco > preco_com_desconto)
);

Note que a restrição de verificação estará satisfeita se o resultado da expressão de verificação for verdade, ou o valor nulo. Como a maioria das expressões retorna o valor nulo quando um dos operandos é nulo, essas expressões não impedem a presença de valores nulos nas colunas com restrição. Para garantir que a coluna não contém valores nulos, deve ser utilizada a restrição de não nulo descrita a seguir.

Uma restrição de não-nulo apenas especifica que a coluna não pode assumir o valor nulo. Um exemplo da sintaxe é:

CREATE TABLE produtos (
    num_produto integer NOT NULL,
    nome text NOT NULL,
    preco numeric
);

Também é possível especificar um nome de restrição explícito, como, por exemplo:

CREATE TABLE produtos (
    num_produto integer NOT NULL,
    nome text CONSTRAINT nome_produto_not_null NOT NULL,
    preco numeric
);

Geralmente uma restrição de não nulo é escrita como uma restrição de coluna. A sintaxe para escrevê-la como uma restrição de tabela é:

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric,
    NOT NULL num_produto,
    NOT NULL nome
);

Mas esta sintaxe não é padrão e destina-se principalmente a ser usada por pg_dump.

Uma restrição de não nulo é funcionalmente equivalente a criar uma restrição de verificação do tipo CHECK (nome_da_coluna IS NOT NULL), mas no PostgreSQL criar uma restrição NOT NULL explícita é mais eficiente.

Obviamente, uma coluna pode ter mais de uma restrição. Basta apenas escrever uma restrição em seguida da outra:

CREATE TABLE produtos (
    num_produto integer NOT NULL,
    nome text NOT NULL,
    preco numeric NOT NULL CHECK (preco > 0)
);

A ordem das restrições não importa, porque não determina, necessariamente, a ordem de verificação das restrições.

Entretanto, uma coluna pode ter no máximo uma restrição explícita de não nulo.

A restrição NOT NULL possui uma inversa: a restrição NULL. Isso não significa que a coluna deva ser sempre nula, o que com certeza não teria utilidade. Em vez disso, apenas seleciona o comportamento padrão de que a coluna pode ser nula. A restrição NULL não é definida no padrão SQL, não devendo ser utilizada em aplicações portáveis (foi somente adicionada ao PostgreSQL para torná-lo compatível com outros sistemas de banco de dados). Porém, alguns usuários gostam, porque facilita inverter a restrição no script de comandos. Por exemplo, é possível começar com

CREATE TABLE produtos (
    num_produto integer NULL,
    nome text NULL,
    preco numeric NULL
);

e depois inserir a palavra-chave NOT onde for desejado.

A restrição de unicidade garante que os dados contidos na coluna, ou no grupo de colunas, são únicos em relação a todas as outras linhas da tabela. A sintaxe é

CREATE TABLE produtos (
    num_produto integer UNIQUE,
    nome text,
    preco numeric
);

quando escrita como restrição de coluna, e

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric,
    UNIQUE (num_produto)
);

quando escrita como restrição de tabela.

Para definir a restrição de unicidade para um grupo de colunas, deve-se escrevê-la como restrição de tabela com os nomes das colunas separados por vírgula:

CREATE TABLE exemplo (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

Isso especifica que a combinação dos valores das colunas indicadas deve ser única para toda a tabela, embora não seja necessário que o valor seja único em cada uma das colunas (o que geralmente não é).

Também é possível atribuir nomes às restrições de unicidade da maneira habitual:

CREATE TABLE produtos (
    num_produto integer CONSTRAINT deve_ser_diferente UNIQUE,
    nome text,
    preco numeric
);

Adicionar uma restrição de unicidade cria, automaticamente, um índice Árvore-B ^[31] na coluna ou grupo de colunas listadas na restrição. Uma restrição de unicidade abrangendo apenas algumas linhas não pode ser escrita como restrição de unicidade, mas é possível impor tal restrição criando um índice parcial com unicidade.

Em geral, uma restrição de unicidade é violada quando há mais de uma linha na tabela em que os valores de todas as colunas incluídas na restrição sejam iguais. Por padrão, dois valores nulos não são considerados iguais nesta comparação. Isto significa que, mesmo na presença de uma restrição de unicidade, é possível armazenar linhas duplicadas que contenham um valor nulo em pelo menos uma das colunas da restrição. Este comportamento pode ser alterado adicionando a cláusula NULLS NOT DISTINCT, como em

CREATE TABLE produtos (
    num_produto integer UNIQUE NULLS NOT DISTINCT,
    nome text,
    preco numeric
);

ou

CREATE TABLE produtos (
    num_produto integer,
    nome text,
    preco numeric,
    UNIQUE NULLS NOT DISTINCT (num_produto)
);

O comportamento padrão pode ser especificado explicitamente usando NULLS DISTINCT. O tratamento padrão de valores nulos em restrições de unicidade é definido pela implementação de acordo com o padrão SQL, enquanto outras implementações têm um comportamento diferente. Portanto, tenha cuidado ao desenvolver aplicações passíveis de portabilidade.

Uma restrição de chave primária indica que uma coluna, ou grupo de colunas, pode ser usado como identificador único para as linhas da tabela. Isso requer que os valores sejam únicos e não nulos. Portanto, as duas definições de tabela a seguir permitem os mesmos dados:

CREATE TABLE produtos (
    num_produto integer UNIQUE NOT NULL,
    nome text,
    preco numeric
);

CREATE TABLE produtos (
    num_produto integer PRIMARY KEY,
    nome text,
    preco numeric
);

As chaves primárias podem incluir mais de uma coluna; a sintaxe é semelhante a das restrições de unicidade:

CREATE TABLE exemplo (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

Adicionar uma chave primária cria, automaticamente, um índice Árvore-B na coluna, ou grupo de colunas, listadas na chave primária e força a(s) coluna(s) a serem marcadas como NOT NULL.

Uma tabela pode ter, no máximo, uma chave primária. (Pode haver qualquer número de restrições de unicidade, que, combinadas com restrições de não-nulo, são funcionalmente quase a mesma coisa, mas apenas uma pode ser identificada como chave primária.) A teoria de banco de dados relacional dita que toda tabela deve ter uma chave primária. Esta regra não é imposta pelo PostgreSQL, mas geralmente é bom segui-la.

As chaves primárias são úteis tanto para fins de documentação, quanto para aplicações cliente. Por exemplo, uma aplicação GUI que permite modificar os valores das linhas, provavelmente precisa conhecer a chave primária da tabela para poder identificar as linhas de forma única. Existem, também, várias maneiras pelas quais o sistema de banco de dados usa uma chave primária, caso tenha sido declarada; por exemplo, a chave primária define a(s) coluna(s) de destino padrão para as chaves estrangeiras que fazem referência à tabela.

A restrição de chave estrangeira especifica que o valor da coluna (ou grupo de colunas) deve corresponder a algum valor existente em uma linha de outra tabela. Dizemos que a chave estrangeira mantém a integridade referencial entre duas tabelas relacionadas.

Supondo termos a tabela produtos utilizada diversas vezes anteriormente:

CREATE TABLE produtos (
    num_produto integer PRIMARY KEY,
    nome text,
    preco numeric
);

Agora vamos assumir a existência de uma tabela que armazena os pedidos desses produtos. Desejamos garantir que a tabela de pedidos contenha somente pedidos de produtos que realmente existem. Para isso é definida uma restrição de chave estrangeira na tabela de pedidos fazendo referência à tabela produtos:

CREATE TABLE pedidos (
    id_pedido integer PRIMARY KEY,
    num_produto integer REFERENCES produtos (num_produto),
    quantidade integer
);

Agora ficou impossível criar um pedido com num_produto que não existe na tabela produtos.

Nessa situação é dito que a tabela pedidos é a tabela que referencia, e a tabela produtos é a tabela referenciada. Da mesma forma, existem colunas fazendo referência e sendo referenciadas.

O comando acima pode ser abreviado escrevendo

CREATE TABLE pedidos (
    id_pedido integer PRIMARY KEY,
    num_produto integer REFERENCES produtos,
    quantidade integer
);

porque, na ausência da lista de colunas, a chave primária da tabela referenciada é usada como coluna referenciada.

Pode ser atribuído um nome à restrição de chave estrangeira, da maneira habitual.

A chave estrangeira também pode aplicar restrição e fazer referência a um grupo de colunas. Como usual, é necessário ser escrito na forma de restrição de tabela. Abaixo está mostrado um exemplo artificial da sintaxe:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES outra_tabela (c1, c2)
);

É claro que o número e tipo de dados das colunas na restrição devem corresponder ao número e tipo de dados das colunas referenciadas.

Às vezes é útil que a “outra tabela” da restrição de chave estrangeira seja a mesma tabela; isto é chamado de chave estrangeira auto-referencial. Por exemplo, se for desejado que as linhas da tabela representem os nós de uma estrutura de árvore, então pode ser escrito:

CREATE TABLE arvore (
    id_no integer PRIMARY KEY,
    id_ancestral integer REFERENCES arvore,
    nome text,
    ...
);

O nó de nível superior teria NULL como id_ancestral, enquanto as entradas não-nulas para id_ancestral só poderiam referenciar linhas válidas da tabela.

Uma tabela pode ter mais de uma restrição de chave estrangeira. Isto é usado para implementar relacionamentos muitos-para-muitos entre tabelas. Digamos que existam tabelas sobre produtos e pedidos, e agora se deseja permitir que um pedido contenha, possivelmente, muitos produtos (o que a estrutura acima não permite). Então poderia ser usada a seguinte estrutura de tabelas:

CREATE TABLE produtos (                       /* Tabela de referência (referenciada) */
    num_produto integer PRIMARY KEY,          /* Coluna de referência (referenciada) */
    nome text,
    preco numeric
);

CREATE TABLE pedidos (                        /* Tabela de referência (referenciada) */
    id_pedido integer PRIMARY KEY,            /* Coluna de referência (referenciada) */
    endereco_entrega text,
    ...
);

CREATE TABLE itens_pedido (                   /* Tabela que faz referência (referenciadora) */
    num_produto integer REFERENCES produtos,  /* Coluna que faz referência (referenciadora) */
    id_pedido integer REFERENCES pedidos,     /* Coluna que faz referência (referenciadora) */
    quantidade integer,
    PRIMARY KEY (num_produto, id_pedido)
);

A ação padrão de ON DELETE é ON DELETE NO ACTION e, portanto, não precisa ser especificada. Isto significa que a exclusão da linhana tabela referenciada pode prosseguir. Entretanto, a restrição de chave estrangeira ainda precisa ser satisfeita, portanto, esta operação geralmente resultará em um erro. Mas a verificação das restrições de chave estrangeira também pode ser adiada para uma etapa posterior da transação. (o que não é abordado neste capítulo). Neste caso, a configuração NO ACTION permitiria que outros comandos “corrigissem” a situação antes que a restrição fosse verificada, por exemplo, inserindo outra linha adequada na tabela referenciada ou excluindo as linhas agora órfãs da tabela referenciadora.

RESTRICT é uma configuração mais restritiva do que NO ACTION. Ela impede a exclusão de uma linha referenciada. RESTRICT não permite que a verificação seja adiada para um momento posterior da transação.

CASCADE especifica que, quando uma linha referenciada é excluída, as linhas referenciadoras também devem ser excluídas automaticamente.

Existem outras duas opções: SET NULL e SET DEFAULT. Estas opções fazem com que a(s) coluna(s) referenciadora(s) seja(m) redefinida(s) para NULL ou para seus valores padrão, respectivamente, quando a linha de referência for excluída. Note que isto não isenta de observar quaisquer restrições. Por exemplo, se uma ação especificar SET DEFAULT mas o valor padrão não satisfizer a restrição de chave estrangeira, a operação irá falhar.

A escolha apropriada da ação ON DELETE depende dos tipos de objetos que as tabelas relacionadas representam. Quando a tabela que faz a referência representa algo que é um componente do que é representado pela tabela de referência e não pode existir independentemente, então CASCADE pode ser apropriado. Se as duas tabelas representam objetos independentes, então RESTRICT ou NO ACTION pode ser mais apropriado; uma aplicação que realmente queira excluir ambos os objetos teria então que ser explícita sobre isto e executar dois comandos de exclusão. No exemplo acima, os itens do pedido fazem parte de um pedido, sendo conveniente que sejam excluídos automaticamente quando o pedido é cancelado. Mas produtos e pedidos são coisas diferentes e, portanto, fazer com que a exclusão de um produto cause automaticamente a exclusão de alguns itens do pedido pode ser considerado problemático. As ações SET NULL ou SET DEFAULT podem ser apropriadas se uma relação de chave estrangeira representar informações opcionais. Por exemplo, se a tabela de produtos fizer uma referência a um gerente de produto e a entrada do gerente do produto for excluída, definir o gerente do produto para este produto como nulo ou um valor padrão pode ser útil.

As ações SET NULL e SET DEFAULT podem receber uma lista de colunas para especificar quais colunas devem ser definidas. Normalmente, todas as colunas da restrição de chave estrangeira são definidas; definir apenas um subconjunto é útil em alguns casos especiais. Veja o seguinte exemplo: ^[32]

CREATE TABLE locadores (
    id_locador integer PRIMARY KEY
);

CREATE TABLE locatários (
    id_locatário integer PRIMARY KEY
);

CREATE TABLE autores (
    id_autor integer PRIMARY KEY
);

CREATE TABLE locações (
    id_locador integer REFERENCES locadores ON DELETE CASCADE,
    id_locatário integer NOT NULL,
    PRIMARY KEY (id_locador, id_locatário)
);

CREATE TABLE mensagens (
    id_locador integer REFERENCES locadores ON DELETE CASCADE,
    id_mensagem integer NOT NULL,
    id_autor integer,
    PRIMARY KEY (id_locador, id_mensagem),
    FOREIGN KEY (id_locador, id_autor)
        REFERENCES locações
        ON DELETE SET NULL (id_autor)
);

INSERT INTO locadores VALUES(1);
INSERT INTO locatários VALUES(1);
INSERT INTO autores VALUES(1);
INSERT INTO locações VALUES(1,1);
INSERT INTO mensagens VALUES(1,1,1);
SELECT * FROM mensagens;

 id_locador | id_mensagem | id_autor
------------+-------------+----------
          1 |           1 |        1
(1 linha)

DELETE FROM locações;
SELECT * FROM mensagens;

 id_locador | id_mensagem | id_autor
------------+-------------+----------
          1 |           1 |
(1 linha)

DELETE FROM locadores;
SELECT * FROM mensagens;

 id_locador | id_mensagem | id_autor
------------+-------------+----------
(0 linha)

Sem a especificação da coluna, a chave estrangeira também definiria a coluna id_locador como nula, mas o valor desta coluna ainda é essencial, porque ela faz parte da chave primária.

De forma análoga a ON DELETE, existe também ON UPDATE, que é chamado quando uma coluna referenciada é modificada (atualizada). As ações possíveis são as mesmas, exceto que não é possível especificar listas de colunas para SET NULL e SET DEFAULT. Neste caso, CASCADE significa que os valores atualizados na(s) coluna(s) referenciada(s) devem ser copiados para a(s) linha(s) referenciadora(s). Há também uma diferença a ser notada entre ON UPDATE NO ACTION (o padrão) e ON UPDATE RESTRICT. A primeira permite que a atualização prossiga e a restrição de chave estrangeira seja verificada em relação ao seu estado após a atualização. A segunda impede a atualização seja executada, mesmo que o estado após a atualização ainda satisfaça à restrição. Isto impede a atualização de uma linha referenciada para um valor que seja distinto, mas que seja considerado igual na comparação (por exemplo, uma cadeia de caracteres com uma variante de maiúsculas e minúsculas diferente, se for usado um tipo de dados de cadeia de caracteres com uma ordenação que não faça diferença entre letras maiúsculas e minúsculas).

Normalmente, uma linha referenciadora não precisa satisfazer a restrição de chave estrangeira se alguma de suas colunas referenciadoras for nula. Se for adicionado MATCH FULL à declaração da chave estrangeira, uma linha referenciadora deixará de satisfazer à restrição somente se todas as suas colunas referenciadoras forem nulas (portanto, uma mistura de valores nulos e não nulos certamente resultará em falha em uma restrição MATCH FULL). Se não for desejado que as linhas referenciadoras possam evitar satisfazer a restrição de chave estrangeira, deve-se declarar a(s) coluna(s) referenciadoras como NOT NULL.

Uma chave estrangeira deve fazer referência a colunas que sejam uma chave primária, formem uma restrição de unicidade, ou sejam colunas de um índice de unicidade não parcial. Isto significa que as colunas referenciadas sempre possuam um índice para permitir buscas eficientes que verifiquem se uma linha referenciadora possui uma correspondência. Uma vez que uma operação DELETE de uma linha da tabela referenciada ou uma operação UPDATE em uma coluna referenciada exigirá uma varredura da tabela referenciadora em busca de linhas que correspondam ao valor antigo, geralmente é uma boa prática indexar também as colunas referenciadoras. Como isto nem sempre é necessário e existem muitas opções disponíveis para indexação, a declaração de uma restrição de chave estrangeira não cria automaticamente um índice nas colunas referenciadoras.

Mais informações sobre como atualizar e excluir dados podem ser encontradas no Capítulo 6. Consulte também a descrição da sintaxe de restrição de chave estrangeira na documentação de referência para CREATE TABLE.

As restrições de exclusão garantem que, se quaisquer duas linhas forem comparadas nas colunas ou expressões especificadas usando os operadores especificados, pelo menos uma dessas comparações de operadores retornará falso ou nulo. A sintaxe é:

CREATE TABLE círculos (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

Veja também CREATE TABLE ... CONSTRAINT ... EXCLUDE para obter mais detalhes.

Adicionar uma restrição de exclusão cria, automaticamente, um índice do tipo especificado na declaração de restrição.