SQL via Linguagem de Definição de Dados

Banco de Dados: DDL na criação de constraints

Prof. Yuri Maluf

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

  1. NOT NULL

  2. UNIQUE

  3. PRIMARY KEY

  4. FOREIGN KEY

  5. DEFAULT

  6. 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:

  1. Estatística: ano e PIB

  2. País: nome, área, moeda, capital, localização da capital

  3. Língua: língua ou idioma

  4. Produto: nome, tipo, ativo

  5. Continente: nome do continente

Modelo Conceitual - Importadora

Modelo Conceitual - Importadora

Modelo Conceitual - Importadora

Modelo Lógico - Importadora

erDiagram
    p[Pais] {
        int id PK
        int id_continente FK
        string nome
        int area 
        string moeda
        string capital
        point localizacao 
    }
    ex[Exporta] {
        int id PK
        int id_pais FK
        int id_produto FK
        decimal preco
        bool seguro
    }
    pr[Produto] {
        int id PK
        string nome
        string tipo
        bool ativo
    }
    c[Continente] {
        int id PK
        string nome
    }
    pl[Pais_Lingua] {
        int id PK
        int id_pais FK
        int id_lingua FK
        bit oficial 
    }
    l[Lingua] {
        int id PK
        string lingua 
    }
    e[Estatistica] {
        int id PK
        int id_pais FK
        year ano
        int PIB
    }


p ||--|{ ex : Exporta
l ||--|{ pl : Falam
c ||--|{ p : Localiza
p ||--|{ pl : Falam
pr ||--|{ ex : Exporta
p ||--o{ e : Divulga 

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)
);