SQL via Linguagem de Definição de Dados

SQL Constraints

Restrições são regras aplicadas nos campos de uma tabela
São usadas para limitar os tipos de dados que são inseridos nas tabelas e garantir a integridade dos dados
Elas são amplamente utilizadas, a exceção rara ocorre quando não há a presença de constraints em uma tabela
Os SGBDs modernos dispõe de diversos tipos de constraints visto sua intensa utilização
Podem ser definidos no momento da criação (CREATE) da tabela ou introduzido posteriormente (ALTER)
Antes de iniciarmos a construção da estrutura o banco de dados do e-commerce, vamos abordar uma estrutura mais simples envolvendo uma importadora. Posteriormente, voltamos ao modelo Físico do e-commerce.

Constraints - Principais Tipos

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
DEFAULT
CHECK

Constraints - NOT NULL

A Constraints do tipo NOT NULL cria uma imposição aos campos uma coluna para não permitir o registro de valores considerados do tipo NULL
Os campos da coluna não preenchidas geram valores NULL
Sua utilização impõe a inserção dados tanto bem como atualizações sem introduzir dados em seus campos e em razão da propriedade ACID das transações de banco de dados SQL todo o registro
Em razão da propriedade ACID das transações de banco de dados SQL a ocorrência de um registro que não satisfaça a restrição fere a atomicidade

coluna datatype NOT NULL;
ALTER TABLE tbl_nome MODIFY coluna varchar(255) NOT NULL;

Constraints - Default

A restrição DEFAULT é usada para definir um valor padrão para uma coluna.
O valor padrão será adicionado a todos os novos registros, se nenhum outro valor for especificado.
Há uma relação próxima com a constraints do tipo NOT NULL

colunaN datatype DEFAULT valor_padrao

Constraints - UNIQUE

As restrições UNIQUE identifica de forma única das registro de forma a garantir que todos os valores em uma coluna sejam diferentes em uma tabala do banco de dados
A constraint UNIQUE e PRIMARY KEY fornecem uma garantia de exclusividade para uma coluna ou conjunto de colunas
Toda restrição do tipo PRIMARY KEY é uma restrição UNIQUE mas o contrário não é verdadeiro.
Uma tabela pode ter muitas restrições UNIQUE por tabela, mas apenas uma restrição PRIMARY KEY por tabela
Uma tabela poder ter uma PRIMARY KEY definida por mais de uma coluna, mas uma restrição UNIQUE é composta por uma única coluna
A título de exemplo, um caso muito comum é decretar a coluna com CPF de clientes, funcionário, fornecedores etc com a restrição UNIQUE

colunaN datatype UNIQUE

Constraints - Primary Key

A constraints do tipo PRIMARY KEY identifica exclusivamente cada registro em uma tabela, exercendo o papel de um atributo identificador
As PRIMARY KEYs devem conter valores únicos e não podem conter valores do tipo NULL.
Uma tabela pode ter apenas uma única chave primária. A chave primária pode consistir em colunas únicas ou múltiplas (campos).

CREATE TABLE tabela_nome(
    pk_column1 type,
    pk_column2 type,
    primary key(pk_column1,pk_column2,)
);

Constraints - Primary Key

Uma prática comum é utilizar variáveis do tipo Integer. As constraints também podem ser combinadas com outros comandos.
Na maioria dos casos o atributo identificador é utilizado conjuntamente com o auto_increment.
No momento da criação da tabela

CREATE TABLE categoria(
    id int auto_increment,
    nome varchar(50) NOT NULL,
    primary key(id)
);

Após a criação da tabela

ALTER TABLE tabela_name [add constraint constraint_nome] primary key (column_list);

Constraints - Foreign Key

A relação entre diversas entidades mapeadas no modelo conceitual e são descritas no modelo lógico por meio da restrição de chave estrangeira FOREIGN KEY e implementada no modelo físico
Uma FOREIGN KEY é um campo (ou coleção de campos) de uma tabela, que realiza um apontamento para uma PRIMARY KEY que se se encontra em uma segunda tabela
A restrição FOREIGN KEY é empregada para evitar ações que destruiriam links entre tabelas, o que não permitiria nenhuma garantia de integridade sobre os dados.
A tabela com a chave estrangeira é chamada de tabela filha, e a tabela com a chave primária é chamada de tabela referenciada ou pai.

ALTER TABLE tabela_nome [constraint constraint_name] foreign key [fk_name](column_list) references parent_table(column_list) [on delete reference_option]
[on update reference_option]);

Constraints - Foreign Key

Primeiro, crie uma nova tabela chamada gadget_types:

create table gadget_types(
    type_id int auto_increment,
    name varchar(100) not null,
    primary key(type_id)
);

Depois, crie outra tabela denominada gadgets:

create table gadgets(
    gadget_id int auto_increment,
    gadget_name varchar(100) not null,
    type_id int,
    primary key(gadget_id),
    constraint fk_type
    foreign key(type_id) 
        references gadget_types(type_id)
);

on delete

Constraints - Check

A restrição CHECK verifica se um valor em uma coluna ou mesmo uma coleção de colunas satisfaz a determinada expressão booleana antes de inserir ou atualizar o dado na tabela.

A sintaxe geral da restrição CHECK do MariaDB pela seguinte consulta:

colunaN int check(colunaN >0),

Estudo de Caso - Importadora

Uma empresa que trabalha no ramo de importação decide adquirir um sistema para auxiliar em suas operações. O futuro sistema da empresa precisa conter algumas informações geográficas de diversos países ao qual a importadora mantém negócios. A equipe contratada para desenvolver o banco de dados após toda a análise de requisitos chega ao modelo conceitual. O modelo obtido apresenta as seguintes entidades:

Estatística: ano e PIB
País: nome, área, moeda, capital, localização da capital
Língua: língua ou idioma
Produto: nome, tipo, ativo
Continente: nome do continente

Modelo Conceitual - Importadora

Modelo Lógico - Importadora

Criação de Tabela e Relacionamento

Chave Primária PK: Pelo modelo lógico, foi fixado nas tabelas uma chave primária PK. Para permitir que o SGBD administre as chaves identificadoras, definimos o campo (id) com valor inteiro e com a opção de incrementos +1 para cada novo registro (nova linha).
Chave Estrangeira FK: Nas tabelas com chaves estrangeiras FK executamos o comando foreign key(campo) para decretar o respectivo campo. Na sequência, realizamos a vinculação da chave estrangeira com o campo da chave primária localizada na tabela que participa do relacionamento. Isso é feito pelo comando reference tabela(chave primaria).
Restrições: Para cada campo das tabelas do diagrama, precisamos ficar atentos para seu tipo de dado e as suas restrições impostas pela situação tais como not null, unique, default value, unsigned etc.
Ordem: É recomendado iniciar a criação das tabelas por ordem crescente do número de chaves estrangeiras.
Evite Point Click: É extremamente importante, ao iniciar seus estudos em modelagem de dados, executar manualmente todos os comandos para desenvolver o modelo físico. Evite o uso de softwares point click, pois o apelo visual para realização de tarefas suprime virtualmente o entendimento da sua estrutura lógica.

Criação de Tabela e Relacionamento

create table continente(
-- id: opcao auto_increment add +1 a cada registro  
    id int auto_increment,
    nome varchar(30) not null,
    primary key (id)
);

create table produto(
    id int auto_increment,
    nome varchar(40) not null,
    tipo varchar(40) not null,
-- ativo: dado equivalente TINYINT(1) e representa VERDADEIRO ou FALSO
    ativo boolean not null default 1,
    primary key (id)
);

create table lingua(
    id int auto_increment,
    nome varchar(30) not null,
-- definicao do campo chave primária
    primary key (id)
);

Criação de Tabela e Relacionamento


create table pais (
    id int auto_increment,
    fk_continente int,
    nome varchar(30),
    -- unsigned: valores somente positivo 
    area int unsigned not null,
    moeda varchar(15),
    capital varchar(30),
    -- point: tipo de dado geoespacial 
    localizacao point,
    primary key(id),
    -- definicao campo como chave estrangeira e apontamento para a chave primária   
    foreign key(fk_continente) references continente(id)
);

Criação de Tabela e Relacionamento

create table exporta(
    id int auto_increment,
    fk_pais int,
    fk_produto int,
    preco decimal(9,2) not null,
    seguro tinyint(1) not null default 1,
    primary key (id),
    foreign key(fk_pais) references pais(id),
    foreign key(fk_produto) references produto(id)
);

create table estatistica(
    id int auto_increment,
    fk_pais int,
    ano year(4),
    PIB int not null default 0,
    primary key (id),
    foreign key(fk_pais) references pais(id)
);

Criação de Tabela e Relacionamento

create table idioma(
    id int auto_increment,
    fk_pais int,
    fk_lingua int,
    oficial boolean not null,
    primary key (id),
    foreign key(fk_pais) references pais(id),
    foreign key(fk_lingua) references lingua(id)
);