2.4 SQL: DDL — restrições de integridade
NOT NULL, DEFAULT e CHECK; CREATE DOMAIN; PRIMARY KEY simples e composta e UNIQUE; FOREIGN KEY com ON DELETE e ON UPDATE; CHECK de tabela e nomes de restrição.
NOT NULL, DEFAULT e CHECK
CREATE TABLE departamento (
dnumero INT NOT NULL CHECK (dnumero > 0 AND dnumero <= 20),
dnome VARCHAR(30) NOT NULL,
cpf_gerente CHAR(11) NOT NULL DEFAULT '00000000000',
data_criacao DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (dnumero)
);Repetir NOT NULL numa coluna que é PK não dá erro; só é redundante.
CREATE DOMAIN
CREATE DOMAIN d_num AS INTEGER
CHECK (VALUE > 0 AND VALUE <= 20);
CREATE TABLE departamento (
dnumero d_num NOT NULL,
dnome VARCHAR(30) NOT NULL,
PRIMARY KEY (dnumero)
);⚠ No vídeo, o CHECK do domínio é ditado sobre "Dnúmero" (o nome da coluna). O domínio ainda não sabe em que coluna será usado: a condição usa VALUE. Com o nome da coluna, o comando falha.
Restrições de chave
PK composta
CREATE TABLE dept_localizacao (
dnumero INT,
dlocalizacao VARCHAR(30),
PRIMARY KEY (dnumero, dlocalizacao)
);Chave alternativa
CREATE TABLE aluno (
matricula INT PRIMARY KEY,
cpf CHAR(11) UNIQUE NOT NULL,
nome VARCHAR(100) NOT NULL
);Errado: dnumero INT PRIMARY KEY, dlocalizacao VARCHAR(30) PRIMARY KEY — seriam duas PKs, e a tabela só pode ter uma.
Integridade referencial e ações engatilhadas
CREATE TABLE empregado (
cpf CHAR(11),
nome VARCHAR(100) NOT NULL,
cpf_supervisor CHAR(11),
dno INT NOT NULL DEFAULT 1,
CONSTRAINT empregado_pk PRIMARY KEY (cpf),
CONSTRAINT supervisor_fk FOREIGN KEY (cpf_supervisor)
REFERENCES empregado (cpf)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT dept_fk FOREIGN KEY (dno)
REFERENCES departamento (dnumero)
ON DELETE SET DEFAULT ON UPDATE CASCADE
);Com ON UPDATE CASCADE, se o departamento 10 virar 11, todos os empregados do 10 passam a apontar para o 11.
O MySQL (InnoDB) aceita a sintaxe SET DEFAULT, mas rejeita a restrição. No PostgreSQL funciona.
⚠ O vídeo diz que a restrição referencial só é violada "quando eu vou alterar valores" da FK ou da PK. O certo é: também violam um INSERT na tabela filha com FK inexistente e um DELETE na tabela pai.
⚠ O vídeo diz que a ação padrão é RESTRICT. No padrão SQL, no PostgreSQL e no MySQL o padrão é NO ACTION: também rejeita, mas a checagem é feita no fim do comando (ou da transação, se a restrição for adiável).
CHECK de tabela e nomes de restrição
CREATE TABLE departamento (
dnumero INT PRIMARY KEY,
dnome VARCHAR(30) NOT NULL,
data_criacao DATE,
inicio_gerente DATE,
CONSTRAINT departamento_datas_ck
CHECK (data_criacao <= inicio_gerente)
);Se uma das datas for NULL, a condição dá "desconhecido" e o CHECK deixa passar. Para exigir as datas, some NOT NULL.
Revisão rápida
Preciso escrever NOT NULL numa coluna PRIMARY KEY?
Não. A PK já implica NOT NULL; repetir só reforça.
Como garantir que o CPF do aluno não se repita, se a PK é a matrícula?
cpf CHAR(11) UNIQUE NOT NULL.
Excluir um departamento com empregados, sem ação declarada, dá o quê?
Erro: a ação padrão (NO ACTION) rejeita a exclusão.
Qual ação faz os empregados seguirem a mudança do número do departamento?
ON UPDATE CASCADE na FK dno.