Restrições de integridade em SQL: Um guia com exemplos

Imagine um banco de dados hospitalar em que as alergias dos pacientes não podem ser deixadas em branco ou um sistema financeiro em que os valores das transações devem ser números positivos. Nesses cenários e em inúmeros outros, contamos com restrições de integridade para garantir que nossos dados permaneçam precisos, consistentes e confiáveis. 

Em SQL, as restrições de integridade são regras que impomos às tabelas de nosso banco de dados para manter a qualidade dos dados. Eles ajudam a evitar erros, aplicam regras comerciais e garantem que nossos dados reflitam as entidades e os relacionamentos do mundo real que eles representam.

Neste artigo, vamos nos aprofundar nos tipos essenciais de restrições de integridade em SQL, fornecendo explicações claras e exemplos práticos para ilustrar seu uso em um banco de dados PostgreSQL. Embora usemos a sintaxe do PostgreSQL, os conceitos são facilmente traduzidos para outros dialetos SQL.

Se você quiser saber mais sobre SQL, confira esta lista de cursos de SQL.

O que são restrições de integridade no SQL?

Considere um cenário em que temos uma tabela que armazena informações do usuário para um aplicativo da Web. Alguns dados, como a idade do usuário, podem ser opcionais porque não impedem que os usuários acessem nosso aplicativo. No entanto, ter uma senha para cada usuário é essencial para fins de login. Para resolver isso, implementaríamos uma restrição de integridade na coluna de senha da tabela do nosso usuário para garantir que cada entrada nessa tabela inclua uma senha.

Em essência, as restrições de integridade são cruciais para:

• Evitar a falta de dados.
• Garantir que todos os dados estejam de acordo com os tipos e intervalos de valores esperados.
• Manter links adequados entre os dados de diferentes tabelas.

Neste artigo, exploraremos as seguintes restrições de integridade essenciais no SQL:

• PRIMARY KEY: Identifica de forma exclusiva cada registro em uma tabela.
• NOT NULL: Garante que uma coluna não possa conter valores NULL.
• UNIQUE: Garante que todos os valores em uma coluna ou grupo de colunas sejam exclusivos.
• DEFAULT: Fornece um valor padrão para uma coluna quando nenhum é especificado.
• CHECK: Garante que todos os valores em uma coluna satisfaçam uma condição específica.
• FOREIGN KEY: Estabelece relações entre tabelas ao fazer referência a uma chave primária em outra tabela.

Caso de estudo: Banco de dados da universidade

Vamos considerar um banco de dados relacional para uma universidade. Esse banco de dados contém três tabelas: students, courses, e enrollments.

Tabela de alunos

A tabela students contém informações sobre todos os estudantes universitários.

• student_id: O identificador do aluno
• first_name: O primeiro nome do aluno.
• last_name: O sobrenome do aluno.
• email: O endereço de e-mail do aluno.
• major: O curso do aluno.
• enrollment_year: O ano em que o aluno se matriculou.

Tabela de cursos

Na tabela courses você encontra informações sobre os cursos disponíveis na universidade.

• course_id: O identificador do curso.
• course_name: O nome do curso.
• department: O departamento do curso.

Tabela de registros

A tabela enrollments armazena informações sobre quais alunos estão matriculados em quais cursos.

• student_id: O identificador do aluno matriculado no curso.
• course_id: O identificador do curso.
• year: O ano da matrícula.
• grade: A nota do aluno nesse curso.
• is_passing_grade: Um booleano que indica se a nota está sendo aprovada.

Ao longo deste artigo, usaremos esse banco de dados de exemplo e mostraremos várias maneiras de impor a integridade dos dados. Usaremos a sintaxe do PostgreSQL em nossas consultas. No entanto, os conceitos são facilmente traduzidos para outros tipos de SQL.

Restrição PRIMARY KEY

O objetivo da universidade é identificar cada aluno de forma exclusiva. Não é aconselhável utilizar o nome e o sobrenome para essa finalidade devido à possibilidade de duplicação de nomes entre os alunos. Da mesma forma, confiar em endereços de e-mail não é o ideal, pois os alunos podem mudar seus e-mails.

A solução comum para garantir a identificação exclusiva é atribuir um identificador exclusivo a cada aluno, que é armazenado na coluna student_id. Podemos aplicar uma restrição PRIMARY KEY à coluna student_id para garantir que cada aluno tenha um identificador exclusivo.

Essa restrição é definida no comando CREATE TABLE, após a especificação do tipo de dados da coluna:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);

A consulta acima cria a tabela de alunos com as seis colunas que mencionamos acima.

A restrição PRIMARY KEY garante que você não terá problemas:

1. Cada aluno tem um student_id.
2. Cada student_id é único.

Restrição PRIMARY KEY em várias colunas

Em determinados cenários, precisamos utilizar várias colunas para identificar cada linha de forma exclusiva. Veja, por exemplo, a tabela enrollments. Um aluno pode se inscrever em vários cursos, o que resulta em várias linhas que compartilham o mesmo student_id. Da mesma forma, um curso pode ter vários alunos inscritos, o que resulta em várias linhas com o mesmo course_id.

Como nenhum campo individual pode identificar exclusivamente uma linha, cada registro de inscrição é determinado usando uma combinação de student_id, course_id e year.

Quando várias colunas estão envolvidas, a restrição PRIMARY KEY é especificada no final do comando CREATE TABLE.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);

Restrições de integridade após a criação da tabela

Há duas maneiras de adicionar restrições de integridade. Acabamos de aprender como fazer isso quando a tabela é criada.

Imagine que a tabela já existe, mas você esqueceu de especificar a restrição PRIMARY KEY. Você também pode defini-lo depois que a tabela for criada usando o comando ALTER TABLE, da seguinte forma:

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);

Na consulta ALTER TABLE, chamamos a restrição de enroll_pk (que significa chave primária de registro). Esse nome pode ser qualquer identificador que você escolher, mas é recomendável selecionar um nome que transmita de forma sucinta o objetivo da restrição.

É uma prática recomendada nomear as restrições de integridade, pois isso proporciona vários benefícios:

• Isso permite uma referência mais fácil, especialmente quando você precisar modificar ou eliminar a restrição no futuro.
• Ele oferece suporte ao gerenciamento e à organização de restrições, especialmente em bancos de dados com um grande número de restrições.

Restrição NOT NULL

A universidade quer garantir que o nome e o e-mail de cada aluno sejam registrados no banco de dados. Eles não querem que a equipe se esqueça de inserir qualquer um desses campos acidentalmente.

Para superar isso, podemos usar as restrições NOT NULL nessas três colunas ao criar a tabela:

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);

A consulta acima usa as restrições NOT NULL para garantir que as colunas first_name, last_name e email não possam ter valores NULL (indefinidos).

Você pode adicionar uma restrição NOT NULL a uma tabela existente usando o comando ALTER TABLE. A sintaxe para adicionar as restrições do site NOT NULL é a seguinte:

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;

Restrição UNIQUE

Os endereços de e-mail são inerentemente exclusivos para cada indivíduo. Em situações em que temos certeza de que um campo nunca deve ter valores duplicados, é uma boa prática impor isso em um nível de banco de dados. Isso ajuda a evitar erros e a garantir a integridade dos dados.

A adição dessa restrição é feita da mesma forma que as outras.

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);

No entanto, em nosso caso, também queremos impor a restrição NOT NULL. Podemos impor várias restrições em uma única coluna separando-as por espaço: 

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);

Observe que a ordem não importa, pois também poderíamos ter usado NOT NULL UNIQUE.

Para adicionar uma restrição UNIQUE a uma tabela existente, você deve fazer o seguinte:

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);

Restrição UNIQUE em várias colunas

Suponhamos que você queira garantir que o nome do curso de cada departamento seja exclusivo na tabela courses. Nesse caso, as colunas course_name e department juntas devem ser exclusivas.

Quando várias colunas estão envolvidas, a restrição é adicionada ao final do comando CREATE TABLE:

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);

Como alternativa, podemos adicionar a restrição alterando uma tabela existente. Nesse caso, fornecemos uma tupla com os nomes das colunas:

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);

Restrições NOT NULL UNIQUE vs. PRIMARY KEY 

Aprendemos que a restrição PRIMARY KEY impõe a unicidade e que não há valores ausentes. Então você deve estar se perguntando qual é a diferença entre:

course_id INT PRIMARY KEY

course_id INT UNIQUE NOT NULL

A diferença entre NOT NULL UNIQUE e PRIMARY KEY em uma tabela é a finalidade e o uso pretendidos. 

Embora ambos imponham a exclusividade e a não nulidade em uma ou mais colunas, uma tabela só pode ter um PRIMARY KEY, que se destina a identificar exclusivamente cada registro na tabela.

Por outro lado, a restrição de combinação NOT NULL UNIQUE pode ser aplicada a colunas adicionais para impor um valor exclusivo em cada linha, sem valores nulos, servindo para manter a integridade dos dados para regras comerciais específicas. Uma tabela pode ter qualquer número de restrições NOT NULL UNIQUE.

A existência de ambos permite maior flexibilidade no design do banco de dados, possibilitando várias maneiras de impor a exclusividade e a integridade dos dados e, ao mesmo tempo, distinguir entre o identificador primário de um registro e outros atributos importantes e exclusivos de uma tabela.

Restrição DEFAULT

Os alunos podem precisar de algum tempo após a matrícula na universidade para selecionar sua área de especialização. A universidade gostaria que o valor da coluna major fosse a string "Undeclared" (não declarado) para os alunos que ainda não selecionaram sua especialização.

Para isso, podemos definir um valor padrão para essa coluna usando a restrição DEFAULT. Você pode alterar a tabela de alunos da seguinte forma:

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';

Se, em vez disso, quisermos definir uma restrição DEFAULT quando a tabela for criada, você poderá fazer isso declarando-a após o tipo de dados da coluna:

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);

Restrição CHECK

Nessa universidade em particular, as notas vão de 0 a 100. Sem nenhuma restrição, a coluna grade da tabela enrollment aceita qualquer valor inteiro. Podemos corrigir isso usando uma restrição CHECK para impor que os valores estejam entre 0 e 100.

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);

Em geral, as restrições do CHECK nos permitem validar condições específicas que queremos que os dados satisfaçam. Isso é importante para garantir a consistência e a integridade dos dados.

Uma restrição CHECK pode envolver mais de uma coluna. Vamos usá-lo para garantir que grade e is_passing_grade tenham valores consistentes. Digamos que uma nota seja aprovada se seu valor for de pelo menos 60. Então, podemos garantir que is_passing_grade é TRUE se e somente se a nota for de pelo menos 60. Vamos fazer isso na criação da tabela para mostrar como as restrições CHECK são declaradas no comando CREATE TABLE:

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);

Há um problema com a restrição acima. Quando um aluno se matricula em um curso, ele ainda não tem uma nota. Portanto, devemos permitir valores de NULL na nota. Ao fazer isso, também precisamos atualizar a restrição de nota de aprovação para que seja NULL quando a nota ainda estiver indefinida. Veja como você pode atualizar a restrição para levar isso em consideração:

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);

Observe que, nas colunas grade e is_passing_grade, adicionamos NULL no tipo de dados e como valor padrão. O objetivo deles é simplesmente aumentar a legibilidade.

Vamos ver agora quais condições podemos impor com uma restrição CHECK.

Condições de alcance

Podemos garantir que os valores em uma coluna estejam dentro de um intervalo específico.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);

Listar condições

Podemos validar se o valor de uma coluna corresponde a um valor em uma lista de valores específicos.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));

Condições de comparação

Podemos comparar valores em uma coluna para garantir que eles atendam a uma condição específica (maior que, menor que etc.).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);

Condições de correspondência de padrões

Podemos usar a correspondência de padrões (por exemplo, com LIKE ou SIMILAR TO) para validar dados de texto.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');

Condições lógicas

Você pode permitir várias condições usando operadores lógicos (AND, OR, NOT).

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);

Condições compostas

Aplica uma condição em várias colunas.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);

Restrição FOREIGN KEY

As restrições de chave estrangeira são usadas para vincular as colunas de duas tabelas, garantindo a integridade referencial dos dados. Em essência, uma chave estrangeira em uma tabela aponta para uma chave primária em outra tabela, indicando que as linhas nessas duas tabelas estão relacionadas. Isso garante que você não possa ter uma linha em uma tabela com uma chave estrangeira que não corresponda a nenhuma linha na tabela vinculada com a chave primária.

Em nosso exemplo, cada registro na tabela de matrículas refere-se a um aluno e a um curso por meio das colunas student_id e course_id, respectivamente. Sem nenhuma restrição, não há nada que garanta que os valores desses identificadores na tabela de matrículas correspondam às entradas existentes nas tabelas de alunos e cursos.

Veja como garantimos isso quando criamos a tabela enrollments:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Observe que, para que sejam chaves estrangeiras, elas devem ser chaves primárias nas tabelas students e courses, respectivamente.

Assim como com outras restrições, também podemos declará-las após a criação da tabela usando o comando ALTER TABLE:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

Colocando tudo junto

Ao longo deste artigo, apresentamos várias restrições de integridade e como usá-las para aprimorar um banco de dados universitário. Aqui está a versão final do comando CREATE TABLE, que combina tudo o que aprendemos.

Primeiro, definiremos a tabela students:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);

A seguir, vamos definir a tabela courses:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);

Por fim, definiremos a tabela enrollments, estabelecendo as relações entre alunos e cursos:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);

Adicionamos algumas restrições neste exemplo final. Uma universidade tem um conjunto conhecido de departamentos e cursos. Portanto, o conjunto de valores que as colunas major e department podem ter é finito e conhecido antecipadamente. Nessas situações, recomenda-se o uso de uma restrição CHECK para garantir que as colunas só possam receber valores desse conjunto conhecido de valores.

Conclusão

Neste artigo, exploramos os diferentes tipos de restrições de integridade no SQL e como implementá-las usando o PostgreSQL. Abordamos chaves primárias, restrições NOT NULL, restrições UNIQUE, restrições DEFAULT, restrições CHECK e restrições FOREIGN KEY, fornecendo exemplos práticos para cada uma delas.

Ao compreender esses conceitos, podemos garantir a precisão, a consistência e a confiabilidade de nossos dados.

Se você quiser saber mais sobre como organizar os dados de forma eficiente, confira este curso sobre Design de banco de dados.