Restrições de Colunas e Modelagem de Relações em Tabelas MySQL

Ao definir uma tabela no MySQL, cada coluna deve especificar um tipo de dados e pode incluir opcionalmente restrições. As restrições adicionam limitações específicas aos tipos de dados, garantindo a integridade dos dados. Este artigo aborda restrições comuns e como modelar relacionamentos entre tabelas.

Para ilustrar, crie um banco de dados para testes:

CREATE DATABASE demo_restricoes;
USE demo_restricoes;

Unsigned e Zerofill

Para colunas numéricas, as restrições unsigned e zerofill são aplicáveis:

unsigned: Remove o bit de sinal, permitindo apenas valores não negativos.
zerofill: Preenche automaticamente com zeros à esquerda até atingir o comprimento especificado. Por exemplo, id INT(5) ZEROFILL garantirá que números com menos de cinco dígitos sejam preenchidos com zeros.

Não Nulo (NOT NULL)

Esta restrição proíbe valores nulos em uma coluna.

CREATE TABLE clientes (
    cliente_id INT,
    nome VARCHAR(20) NOT NULL
);
DESCRIBE clientes;

Ao inserir dados, a coluna nome não pode ser omitida ou receber NULL:

-- Falha, pois 'nome' é obrigatório:
INSERT INTO clientes(cliente_id) VALUES(1);
-- Sucesso, pois uma string vazia não é NULL:
INSERT INTO clientes VALUES(1, '');

Valor Padrão (DEFAULT)

Define um valor padrão quando nenhum é fornecido durante a inserção.

CREATE TABLE pedidos (
    pedido_id INT,
    status VARCHAR(15) DEFAULT 'pendente',
    tipo ENUM('online','presencial') DEFAULT 'online'
);
DESCRIBE pedidos;

Inserções que omitirem colunas com DEFAULT usarão o valor especificado:

INSERT INTO pedidos(pedido_id) VALUES(101);
INSERT INTO pedidos(pedido_id, tipo) VALUES(102, 'presencial');
SELECT * FROM pedidos;

Único (UNIQUE)

Único em Coluna Única

Garante que todos os valores na coluna sejam distintos.

CREATE TABLE emails (
    usuario_id INT UNIQUE,
    email VARCHAR(50) UNIQUE
);
DESCRIBE emails;

Violações de unicidade geram erros:

INSERT INTO emails VALUES(1, 'user@example.com');
-- Erro: chave duplicada para usuario_id
INSERT INTO emails VALUES(1, 'outro@example.com');
-- Erro: chave duplicada para email
INSERT INTO emails VALUES(2, 'user@example.com');

Único Composto

A combinação de valores em múltiplas colunas deve ser única, mas colunas individuais podem se repetir.

CREATE TABLE acessos (
    registro_id INT,
    endereco_ip CHAR(15),
    porta INT,
    UNIQUE(endereco_ip, porta)
);
DESCRIBE acessos;

INSERT INTO acessos VALUES(1, '192.168.0.1', 8080);
INSERT INTO acessos VALUES(2, '192.168.0.1', 8081); -- Permitido
INSERT INTO acessos VALUES(3, '10.0.0.1', 8080);   -- Permitido
-- Erro: combinação duplicada
INSERT INTO acessos VALUES(4, '192.168.0.1', 8080);

Chave Primária (PRIMARY KEY)

Uma chave primária combina as restrições UNIQUE e NOT NULL, servindo como identificador exclusivo para registros. No mecanismo InnoDB, toda tabela deve ter uma chave primária para otimizar o armazenamento e a consulta de dados.

CREATE TABLE usuarios (
    usuario_id INT PRIMARY KEY,
    nome VARCHAR(25)
);
DESCRIBE usuarios;

Inserções duplicadas ou com NULL falham:

INSERT INTO usuarios VALUES(1, 'Ana');
-- Erro: chave primária duplicada
INSERT INTO usuarios VALUES(1, 'Carlos');
-- Erro: valor nulo proibido
INSERT INTO usuarios VALUES(NULL, 'Maria');

Auto Incremento (AUTO_INCREMENT)

Automaticamente gera valores sequenciais para uma coluna, frequentemente usada com chaves primárias.

CREATE TABLE produtos (
    produto_id INT PRIMARY KEY AUTO_INCREMENT,
    descricao VARCHAR(50)
);
DESCRIBE produtos;

Ao inserir, omita a coluna auto-incrementada:

INSERT INTO produtos(descricao) VALUES('Teclado'), ('Mouse');
SELECT * FROM produtos;

Notas importantes:

Excluir registros não reutiliza valores anteriores.
Inserir manualmente um valor alto fará com que a sequência continue a partir dele.

Relações entre Tabelas com Chave Estrangeira

Chaves estrangeiras (FOREIGN KEY) estabelecem links entre tabelas, garantindo integridade referencial. Elas podem incluir cláusulas como ON UPDATE CASCADE e ON DELETE CASCADE para sincronizar alterações.

Relação Um-para-Muitos

Exemplo: um departamento pode ter vários funcionários. A tabela de funcionários terá uma coluna referenciendo a chave primária da tabela de departamentos.

-- Tabela referenciada (departamentos)
CREATE TABLE departamentos (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    nome_depto VARCHAR(30),
    descricao VARCHAR(100)
);

-- Tabela com chave estrangeira (funcionarios)
CREATE TABLE funcionarios (
    func_id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(25),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departamentos(dept_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Inserção de dados:

INSERT INTO departamentos(nome_depto, descricao) VALUES
    ('Engenharia', 'Desenvolvimento de sistemas'),
    ('Marketing', 'Promoção e vendas');

INSERT INTO funcionarios(nome, dept_id) VALUES
    ('João', 1), ('Maria', 1), ('Pedro', 2);

Com CASCADE, alterações ou exclusões em departamentos propagam-se para funcionários.

Relação Muitos-para-Muitos

Requer uma tabela intermediária. Exemplo: autores e livros.

CREATE TABLE autores (
    autor_id INT PRIMARY KEY AUTO_INCREMENT,
    nome_autor VARCHAR(40)
);

CREATE TABLE livros (
    livro_id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(60)
);

-- Tabela intermediária
CREATE TABLE autoria (
    autoria_id INT PRIMARY KEY AUTO_INCREMENT,
    autor_id INT,
    livro_id INT,
    FOREIGN KEY (autor_id) REFERENCES autores(autor_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (livro_id) REFERENCES livros(livro_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

Relação Um-para-Um

Útil para separar dados frequentemente acessados de dados menos usados (otimização de consulta).

CREATE TABLE detalhes_usuario (
    detalhe_id INT PRIMARY KEY AUTO_INCREMENT,
    telefone BIGINT,
    endereco VARCHAR(100)
);

CREATE TABLE perfil_usuario (
    perfil_id INT PRIMARY KEY AUTO_INCREMENT,
    nome_usuario VARCHAR(30),
    detalhe_id INT UNIQUE,
    FOREIGN KEY (detalhe_id) REFERENCES detalhes_usuario(detalhe_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

A coluna UNIQUE em detalhe_id garante a relação um-para-um.

Tags: MySQL SQL Restrições de Colunas chave estrangeira Relacionamentos de Tabelas

Publicado em 6-15 04:46 por Thomas

Doido Dev