Restringindo a entrada de dados com SQL
Veja como funcionam as constraints dos bancos de dados SQL
Neste artigo vamos ver como criar restrições para inserir dados em tabelas dos banco de dados MySQL e SQL Server. Para fazer isso, vamos aprender a como trabalhar com constraints.
As constraints são restrições aplicadas no momento de inserir informações no banco de dados.
Os tipos de contraints mais comuns são as chaves primárias (primary keys) e chaves estrangeiras (foreign key)
Este artigo vai abordar outros tipos de constraints. Você vai ver exemplos de como trabalhar com as seguintes constraints:
NOT NULL
UNIQUE
CHECK
Os exemplos a seguir foram testados no MySQL versão 8.0 e no SQL Server Express 2017. Você pode conferir a versão do seu banco de dados executando o seguinte comando:
SELECT @@version
Vamos começar trabalhando com UNIQUE e NOT NULL.
UTILIZANDO UNIQUE E NOT NULL
Vamos criar tabelas de exemplo para praticar estes conceitos:
MySQL
CREATE TABLE Empresas (
ID int NOT NULL AUTO_INCREMENT,
Nome varchar(255),
CNPJ varchar(18),
Telefone varchar(30) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT CHK_CNPJ UNIQUE(CNPJ)
);
SQL Server
CREATE TABLE Empresas (
ID int IDENTITY(1,1) PRIMARY KEY,
Nome varchar(255),
CNPJ varchar(18),
Telefone varchar(30) NOT NULL,
CONSTRAINT CHK_CNPJ UNIQUE(CNPJ)
);
Na tabela empresas, criamos duas regras, a primeira é que o telefone não pode ser nulo. A segunda é o CNPJ precisa ser único.
Vamos colocar a prova estas regras:
INSERT INTO Empresas (Nome, CNPJ) VALUES ("ACME", "83.168.738/0001-90");
Este registro não foi inserido porque não informamos o telefone. Ao informar um telefone o registro deve ser inserido:
INSERT INTO Empresas (Nome, CNPJ, Telefone) VALUES ("ACME", "83.168.738/0001-90", "(11)234-5678");
Vamos testar agora a restrição de inserir um novo registro com o mesmo CNPJ de uma empresa já cadastrada no banco
INSERT INTO Empresas (Nome, CNPJ, Telefone) VALUES ("Empresa 1","28.987.201/0001-43", "(11) 342-2222");
INSERT INTO Empresas (Nome, CNPJ, Telefone) VALUES ("Empresa 2","78.755.533/0001-70", "(11) 421-2212");
INSERT INTO Empresas (Nome, CNPJ, Telefone) VALUES ("Empresa 3","83.168.738/0001-90", "(11)234-5678");
O último registro não foi inserido porque o seu número de CNPJ é igual ao primeiro registro que inserimos na tabela empresas.
UTILIZANDO CHECK
Até agora vimos como impedir repetições de campo ou a inserção de campos nulos. Mas como podemos criar restrições com base no conteúdo do registro?
Uma das formas de restringir certos conteúdos é utilizar uma constraint chamada CHECK.
Para ver isso na prática vamos criar outra tabela
MySQL
CREATE TABLE Alunos (
ID int NOT NULL AUTO_INCREMENT,
Nome varchar(255),
Idade int,
PRIMARY KEY (ID),
CHECK (Idade>=18)
);
SQL Server
CREATE TABLE Alunos (
ID int IDENTITY(1,1) PRIMARY KEY,
Nome varchar(255),
Idade int CHECK (Idade>=18)
);
Agora que criamos as tabelas, vamos inserir alguns registros.
INSERT INTO Alunos (Nome, Idade) VALUES ("Lucas", 34);
INSERT INTO Alunos (Nome, Idade) VALUES ("João", 24);
INSERT INTO Alunos (Nome, Idade) VALUES ("Matheus", 28);
INSERT INTO Alunos (Nome, Idade) VALUES ("Thiago", 17);
Perceba que ao inserir estes registros, o último não foi registrado, já que ele vai contra a regra definida na criação da tabela, que era a idade igual ou superior a 18 anos
Você pode criar regras onde várias colunas sejam utilizadas. Vamos apagar a nossa tabela de testes utilizando o seguinte comando
DROP TABLE Alunos
E vamos criar novamente, com algumas colunas a mais
MySQL
CREATE TABLE Alunos (
ID int NOT NULL AUTO_INCREMENT,
Nome varchar(255),
Idade int,
Curso varchar(255),
Cidade varchar(255),
PRIMARY KEY (ID),
CONSTRAINT CHK_Aluno CHECK (Idade>=18 AND Curso="MySQL")
);
SQL Server
CREATE TABLE Alunos (
ID int IDENTITY(1,1) PRIMARY KEY,
Nome varchar(255),
Idade int,
Curso varchar(255),
Cidade varchar(255),
CONSTRAINT CHK_Aluno CHECK (Idade>=18 AND Curso="MySQL")
);
Utilizando CONSTRAINT definimos duas regras para inserir registros, a primeira é que o campo idade seja igual ou maior que 18, e que o curso seja igual a MySQL. Vamos inserir registros para testar essa nova regra.
INSERT INTO Alunos (Nome, Idade, Curso) VALUES ("Lucas", 34, "SQL Server");
INSERT INTO Alunos (Nome, Idade, Curso) VALUES ("João", 24, "MySQL");
INSERT INTO Alunos (Nome, Idade, Curso) VALUES ("Matheus", 28, "MySQL");
INSERT INTO Alunos (Nome, Idade, Curso) VALUES ("Thiago", 17, "MySQL");
Nos testes a seguir o primeiro e o último registro não foram inseridos
Podemos excluir essa restrições utilizamos o seguinte comando:
MySQL
ALTER TABLE Alunos
DROP CHECK CHK_Aluno;
SQL SERVER
ALTER TABLE Alunos
DROP CONSTRAINT CHK_Aluno;
Utilizando o ALTER TABLE também podemos adicionar uma restrição numa tabela existente. Vamos colocar uma restrição no campo Cidade:
ALTER TABLE Alunos
ADD CHECK (Cidade = 'Santos');
Para saber quais as constraints que foram aplicadas numa determinada tabela você pode utiizar a seguinte consulta:
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='Alunos';