Delete Restrict / Cascade / Set NULL

Olá!

Para que a integridade do banco de dados (DB) seja mantida, há algumas regras que auxiliam a proteger as associações de registros que são realizadas entres as tabelas. Neste post será demonstrado algumas dessas regras que possuem relação quanto a exclusão registros que possuem ligação em outra tabela.

De modo, há apresentar o impacto que cada regra possui sobre a associação das tabelas e seus respectivos registros.

Temos, as seguintes regras:

• Delete Restrict

Não permite deletar registros "pai" que possuem ligação (por meio de FK) em outra tabela.

• Delete Cascade

Deleta o registro na "tabela pai" e automaticamente todos os demais registros associados a ele nas demais tabelas do DB.

• Delete SET NULL

Ao deletar o registro na "tabela pai" que está sendo referenciado em outras tabelas, a coluna(s) de ligação (FK) na outra tabela tem o valor alterado nulo (já que a FK não vai existir mais, pois o registro pai foi deletado).

Para os exemplos, é utilizado a tabela carro e marca_carro, sendo que elas estão ligadas pela coluna cd_marca_carro. Deste modo, vários carros podem ser de uma marca. Veja abaixo o modelo de entidade e relacionamento (EER) das tabelas:

Modelo EER para carro e marca_carro

Neste modelo podemos deletar qualquer registro da tabela carro ("tabela filha") sem complicações. Entretanto, ao executar um SQL para excluir alguma marca de carro que esteja associada a algum carro será aplicado a regra definida na FK da tabela carro.

A tabela marca_carro neste modelo possui a ligação de ser a "tabela pai", assim ela foi criada primeiro com a seguinte instrução SQL:

           CREATE TABLE `blog`.`marca_carro` (

                        `cd_marca_carro` INT         NOT NULL

                      , `nm_marca_carro` VARCHAR(45) NOT NULL

                      , PRIMARY KEY (`cd_marca_carro`));

Já a tabela carro, foi criada posteriormente com o SQL:

           CREATE TABLE `blog`.`carro` (

                        `cd_carro`       INT         NOT NULL

                      , `nm_carro`       VARCHAR(45) NOT NULL

                      , `cd_marca_carro` INT         NOT NULL

                      , PRIMARY KEY (`cd_carro`)

                      , INDEX `cd_marca_carro_idx` (`cd_marca_carro` ASC)

             CONSTRAINT `cd_marca_carro`

            FOREIGN KEY (`cd_marca_carro`)

             REFERENCES `blog`.`marca_carro` (`cd_marca_carro`)

                     ON DELETE RESTRICT

                     ON UPDATE NO ACTION);

A definição da regra a ser aplicada caso seja executado uma instrução DELETE na tabela marca_carro está neste caso na penúltima linha: ON DELETE RESTRICT.

Com os seguintes registros abaixo em cada tabela, é demonstrado os exemplos do impacto de cada regra ao ser executado instruções DELETE:

Tabela carro	Tabela marca_carro

Note que o primeiro e segundo registro da tabela carro está associado a marca Fiat, e o carro com código 3 está associado a marca Jeep. Também, veja que não há nenhum carro associado a marca Mercedes.

• Impacto com a regra Delete Restrict

Ao executar o comando para deletar a marca Fiat que possui associação com dois carros, SQL: 

           DELETE FROM marca_carro WHERE cd_marca_carro = 1;

Resultado:

Não possível deletar o registro de marca_carro, pois ele está sendo referenciado na tabela carro. Para que o SQL Delete aconteça, é necessário primeiramente que os registros que fazem referencia ao registro da tabela marca_carro sejam deletados ou atualizados para fazer referência a outro registro. Ou seja, o registro da tabela marca_carro somente será deletado se não estiver sendo referenciado em outra tabela.

Já ao executar o comando para excluir a marca Mercedes que não tem nenhuma ligação, SQL:

           DELETE FROM marca_carro WHERE cd_marca_carro = 3;

Resultado:

Deletado com sucesso pois não está sendo referenciado em nenhuma tabela.

Assim, após a execução das instruções Delete, a tabela marca_carro fica com os seguintes registros:

Tabela: marca_carro

• Impacto com a regra Delete Cascade

Ao executar o comando para deletar a marca Fiat, SQL:

           DELETE FROM marca_carro WHERE cd_marca_carro = 1;

Automaticamente todos os registros da tabela carro vinculados a marca Fiat também são deletados, veja o estado de cada tabela após a execução do SQL :

Tabela marca_carro tem a marca Fiat deletada com sucesso:

Tabela: marca_carro

A tabela carro possui a exclusão automática de todos os registros associados a marca deletada:

Tabela: carro

• Impacto com a regra Delete SET NULL

Primeiramente é necessário habilitar que a coluna cd_marca_carro na tabela carro possa receber valor nulo, pois note que no comando de criação da tabela há a definição de NOT NULL para essa coluna.

Ao executar o SQL:

           DELETE FROM marca_carro WHERE cd_marca_carro = 1;

A marca de código 1 é deletada com sucesso, e automaticamente todos os registros da tabela carro que faziam referência ao registro deletado teve o valor da coluna cd_marca_carro alterado para nulo, conforme:

Tabela: carro

Além dessas três regras, existe o SET DEFAULT que é semelhante ao SET NULL, porem atribui um valor definido para a FK dos registros associados ao registro "pai" deletado. 

E por fim, se nenhuma dessas regras forem atribuídas, a definição da tabela constará com:

ON DELETE NO ACTION. Assim, nenhuma ação/verificação é executada.

Observação: verifique se o mecanismo de armazenamento do DB permite a utilização dessas regras. Pois, por exemplo, o MySql com InnoDB não permite usar ON DELETE SET DEFAULT.

Consultar a relação de Termos e Abreviaturas acesse aqui.

FELIPE BARBOSA FERREIRA

Até o próximo post!

Função CONCAT() - Concatenar

Olá!

Na tabela de exemplo abaixo temos o armazenamento do nome e sobrenome da pessoa em colunas diferentes, veja:

PESSOA
CD_PESSOA	NOME	SOBRENOME	IDADE
1	Felipe	Barbosa	23
2	Ana	Vieira	20
3	João	Santos	19
4	Aline	null	21
5	Maira	Dantas	25
6	Ana	null	19
7	Aline	Ferreira	22

Deste modo, se a aplicação necessitar apresentar o nome completo da pessoa em uma única linha, será realizado a concatenação dos valores dessas colunas pelo front-end (HTML; PHP; Java; etc), ou, previamente pela instrução SQL.

Para concatenar valores de colunas no SQL utilizamos a função CONCAT(), sintaxe:

• CONCAT (expressão-1, expressão-2, ...)

Onde, "expressão" pode ser um valor fixo, ou uma coluna, ou o resultado de outra função.

Exemplos:

1) - Concatenar as colunas NOME e SOBRENOME

SQL:

SELECT CONCAT(NOME, SOBRENOME)

  FROM PESSOA;

Resultado:

1. FelipeBarbosa
2. AnaVieira
3. JoãoSantos
4. null
5. MairaDantas
6. null
7. AlineFerreira

2) - Concatenar coluna com valor fixo

SQL:

SELECT CONCAT(NOME, " valorFixoTeste")

  FROM PESSOA

 WHERE CD_PESSOA = 1;

Resultado:

1. Felipe valorFixoTeste

3) - Concatenar coluna com o resultado de outra função

SQL:

SELECT CONCAT(NOME, COALESCE(SOBRENOME, " Sobrenome não cadastrado"))

  FROM PESSOA;

Resultado:

1. FelipeBarbosa
2. AnaVieira
3. JoãoSantos
4. Aline Sobrenome não cadastrado
5. MairaDantas
6. Ana Sobrenome não cadastrado
7. AlineFerreira

Note que com o resultado dos exemplos, podemos constatar algumas características da função CONCAT(), são:

• Os valores das colunas são concatenados sem a adição de espaços. No primeiro exemplo o nome e sobrenome ficaram juntos.
• Quando o valor de uma expressão é nulo, o resultado da concatenação é nulo.

Para adicionar o espaço entre o nome e sobrenome, veja a solução:

SQL:

SELECT CONCAT(NOME, " ", SOBRENOME)

  FROM PESSOA;

Consultar a relação de Termos e Abreviaturas acesse aqui.

Felipe Barbosa Ferreira

Até o próximo post!

Função COALESCE( )

Olá!

Ao executar uma instrução SQL que esteja manipulando colunas que possuem valores nulos, é possível realizar a troca do valor nulo por outro valor, isso em tempo de execução, ou seja, não é realizado o update do valor na base de dados.

Tabela de exemplo:

PESSOA
CD_PESSOA	NOME	SOBRENOME	IDADE
1	Felipe	Barbosa	23
2	Ana	null	20
3	João	Santos	19
4	Aline	null	21
5	Maira	null	25
6	Ana	Havana	19
7	Aline	Ferreira	22

Na tabela PESSOA há registros com valor null para a coluna SOBRENOME. Com a função COALESCE() em uma instrução Select podemos fazer com que seja retornado "Sobrenome não cadastrado" para os registros que possuírem valor null.

Sintaxe:

• COALESCE (expressão-1, expressão-2, ...)

Caso a primeira expressão tenha valor nulo, será retornado o valor da próxima expressão que não seja nulo.

Exemplo:

Trocar o valor null pelo texto: "Sobrenome não cadastrado", para os registros que possuam nulo na coluna SOBRENOME.

SQL:

SELECT NOME
, COALESCE(SOBRENOME, "Sobrenome não cadastrado")
, IDADE
FROM PESSOA;

Resultado:

NOME	SOBRENOME	IDADE
Felipe	Barbosa	23
Ana	Sobrenome não cadastrado	20
João	Santos	19
Aline	Sobrenome não cadastrado	21
Maira	Sobrenome não cadastrado	25
Ana	Havana	19
Aline	Ferreira	22

Consultar a relação de Termos e Abreviaturas acesse aqui.

Felipe Barbosa Ferreira

Até o próximo post!

With Default - Valor padrão para a coluna

Olá!

O comando With Default é utilizado para definir um valor padrão a uma coluna, ele entra em ação quando é feito a inclusão de um registro sem atribuir valor a coluna.

Veja o exemplo abaixo da utilização desse comando para definir o valor padrão para a coluna SOBRENOME e DATA_INSCRICAO:

       CREATE TABLE INSCRICOES

           (

               CODIGO_PESSOA  INT         NOT NULL

           ,   NOME           VARCHAR(30) NOT NULL

           ,   SOBRENOME      VARCHAR(50) WITH DEFAULT 'em branco'

           ,   DATA_INSCRICAO DATE        WITH DEFAULT GETDATE()

           )

Deste modo, se na inserção de um registro na tabela de INSCRICOES não ser informado o sobrenome ou a data de inscrição, será respectivamente atribuído o valor 'em branco' ou a data atual do sistema para a coluna cujo valor não foi especificado.

Para criar o valor padrão a coluna de uma tabela existente ou alterar o valor definido pelo comando With Default, é necessário usar o comando Alter Table, veja:

       ALTER TABLE INSCRICOES

       ALTER SOBRENOME SET DEFAULT 'Não informado'

Após a execução do comando acima, será atribuído o valor 'Não informado' para a coluna SOBRENOME quando não definido um valor a está coluna no momento da inserção.

O procedimento da exclusão do valor padrão é semelhante ao processo de alteração, veja o comando abaixo para excluir a definição feita pelo With Default:

       ALTER TABLE INSCRICOES

       ALTER SOBRENOME DROP DEFAULT

Consultar a relação de Termos e Abreviaturas acesse aqui.

Felipe Barbosa Ferreira

Até o próximo post!

Alter table

Olá!

O comando ALTER TABLE é utilizado com o propósito de realizar alterações em uma tabela do banco de dados. Assim, com esse comando é possível adicionar, deletar ou modificar as colunas de uma tabela.

De modo que as modificações realizadas com o ALTER TABLE podem afetar a estrutura física da tabela, e também as regras (constraints) que são aplicadas em cada coluna. Por exemplo, para adicionar ou remover a regra de que o campo não pode ser nulo em uma tabela já existente é necessário utilizar o ALTER TABLE.

Alguns exemplos da aplicação do ALTER TABLE:

• Adicionar uma coluna na tabela, sintaxe:

ALTER TABLE nome_da_tabela ADD nome_da_nova_coluna tipo_de_dado_da_coluna

ALTER TABLE pessoa         ADD sobrenome           VARCHAR (50)

• Remover uma coluna da tabela, sintaxe:

ALTER TABLE nome_da_tabela DROP COLUMN nome_da_coluna

ALTER TABLE pessoa         DROP COLUMN sobrenome

• Modificar o tamanho do campo da tabela, sintaxe:

ALTER TABLE nome_da_tabela ALTER COLUMN nome_da_coluna novo_tipo_de_dado

ALTER TABLE pessoa         ALTER COLUMN sobrenome      VARCHAR (80)

Fique atento a sintaxe do comando para o banco de dados em que você está trabalhando, pois há pequenas particularidades na sintaxe deste comando para cada banco de dados. Por exemplo, veja abaixo como seria a sintaxe para modificar o tipo de dado da coluna de uma tabela no MySQL, DB2, PostgreSQL:

MySQL

ALTER TABLE nome_da_tabela

  MODIFY COLUMN nome_da_coluna  novo_tipo_de_dado

DB2

ALTER TABLE nome_da_tabela

  ALTER COLUMN nome_da_coluna SET DATA TYPE novo_tipo_de_dado

PostgreSQL

ALTER TABLE nome_da_tabela

  ALTER COLUMN nome_da_coluna TYPE novo_tipo_de_dado

O comando possui vários sub-comandos para diversos tipos de operações, e cada banco de dados possui uma particularidade para a sintaxe desses sub-comandos. Para visualizar todos os sub-comandos disponíveis visite os links a seguir:

ALTER TABLE no MySQL

ALTER TABLE no DB2

ALTER TABLE no PostgreSQL

Note também que em alguns bancos de dados algumas operações não são permitidas, e em outros são. Então, dependendo da aplicação a ser desenvolvida/trabalhada a escolha do banco de dados pode ser definida com base nessas operações, exemplo: em uma aula de SQL, os bancos de dados que permitem mais operações podem demonstrar melhor a funcionalidade de cada sub-comando do ALTER TABLE.

Consultar a relação de Termos e Abreviaturas acesse aqui.

Felipe Barbosa Ferreira

Até o próximo post!

Constraints - Restrições/Regras

Olá!

No SQL as constraints (restrições / regras) servem para definir regras para os dados que podem ser armazenados em uma coluna. Uma constraint pode ser simples como a restrição de um campo não receber valor nulo, ou a constraint pode ser complexa de modo a avaliar um ou mais campos, e com uma ou mais condições.

Quando a constraint não é respeitada a ação que está sendo executada é abortada, por exemplo: tentar inserir um novo registro com valor nulo em campo com a restrição NOT NULL.

Alguns exemplos de constraint:

• NOT NULL: define que o campo da tabela não recebe valor nulo
• DEFAULT: especifica um valor padrão para o campo (quando não informado)
• UNIQUE: determina que cada registro da tabela deve conter um valor único

A constraint de um campo pode ser criada no momento da criação da tabela (Create table), ou também em uma tabela já existente (Alter table), segue os exemplos:

Definir constraint na criação da tabela:

       CREATE TABLE PESSOA

           (

               CODIGO_PESSOA  INT         NOT NULL

           ,   NOME           VARCHAR(30)

           ,   SOBRENOME      VARCHAR(50) WITH DEFAULT 'Não informado'

           ) 

Definir constraint em uma tabela existente:

       ALTER TABLE PESSOA

       ALTER NOME SET NOT NULL

Consultar a relação de Termos e Abreviaturas acesse aqui.

Felipe Barbosa Ferreira

Até o próximo post!

Null Values

Olá!

Por padrão campos de uma tabela de um banco de dados (DB) podem suportar valores nulos, ou seja, é opcional designar um valor para o campo.

Sendo que quando o campo está NULL (valor nulo), ele é tratado diferente dos outros campos que possuem valores, pois NULL é usado como um espaço reservado para valores desconhecidos ou não determinados.

Assim, é possível inserir ou atualizar registros do DB com a designação ou não de valor para os campos que aceitam valor nulo. Quando um registro é salvo com um campo sem valor, isto significa que o campo está NULL.

E ao realizar a pesquisa em uma tabela que retorne registros que possuam valores nulos, a representação do campo que estar nulo na maior parte dos DB é feita com espaços, conforme:

PESSOA
CD_PESSOA	NOME	APELIDO
1	Felipe	FelipeB
2	Ana
3	João
4	Aline	AlineS
5	Maira	MairaD

Como os campos nulos recebem um tratamento diferente dos demais campos que possuem valor, eles não podem ser testados com os operadores relacionais (<, >, =, <=, >=, <>). Para realizar uma consulta que verifique se o valor do registro a ser selecionado possui valor nulo ou não é necessário usar os operadores IS NULL ou IS NOT NULL.

• IS NULL

O operador IS NULL é usado quando a consulta no DB tem o objetivo de selecionar os registros que possuem valor nulo. Exemplo ao executar o SQL abaixo com este operador na tabela PESSOA:

SELECT NOME
     , APELIDO

  FROM PESSOA

 WHERE APELIDO IS NULL;

Temos o seguinte resultado:

reposta_da_consulta
NOME	APELIDO
Ana
João

• IS NOT NULL

O operador IS NOT NULL é usado com o objetivo de selecionar registros de uma tabela que não possuam valor nulo, exemplo do SQL abaixo:

SELECT NOME
     , APELIDO

  FROM PESSOA

 WHERE APELIDO IS NOT NULL;

O resultado:

reposta_da_consulta
NOME	APELIDO
Felipe	FelipeB
Aline	AlineS
Maira	MairaD

Enfim, tenham atenção pois algumas interfaces/IDE utilizadas para manipular os dados dos DB, não representam o campo com valor nulo com espaço, assim caso tenha algum caractere "estranho" na resposta da sua consulta, verifique se o campo está com valor null.

Consultar a relação de Termos e Abreviaturas acesse aqui.

Felipe Barbosa Ferreira

Até o próximo post!