Implementando Herança de Tabela no SQL Server – Parte 03

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Finaliznado o artigo sobre como implementar herança de tabela no SQL Server.

Implementando Herança de Tabela no SQL Server – Parte 03

Por: Jeff Smith

Modelando Restrições Um-para-Muitos

No entanto, o que acontece se desejarmos configurar nosso modelo para que uma Pessoa possa ser somente de um tipo, por exemplo, um Estudante OU um Professor OU um Pai? Isto é uma implementação mais correta de herança de um banco de dados, pois em POO você não pode criar uma instância de algo que é mais que uma sub-classe ao mesmo tempo – você deve escolher uma ou outra (ou potencialmente a classe base em si, se for o que você deseja).

Modelar isso pode ser uma armadilha, e pode ser feito de diversas formas. Aqui será apresentada uma técnica que achamos interessante, pois ela não requer uma trigger, é fácil de ser implementada e usa integridade referencial padrão sem restrições de verificação complicadas.

Vamos lá: primeiro crie uma tabela de TiposPessoa (Listagem 3):

Listagem 3. Criando a tabela TiposPessoa

create table TiposPessoa (TipoPessoaID int primary key, TipoPessoa varchar(10))

E vamos inserir alguns valores nesta tabela... um por “sub-tabela” (Listagem 4):

Listagem 4. Inserindo dados de exemplo na tabela TiposPessoa
insert into TiposPessoa
select 1,'Estudante' union all
select 2,'Professor' union all
select 3,'Pai'

Esta é a tabela que define os diferentes tipos de pessoa que o nosso sistema irá permitir.
Existirá um registro nesta tabela para cada "sub-tabela" que criamos. Se você desejar permitir
que uma pessoa seja apenas uma “Pessoa” genérica sem qualquer sub-tipo específico, nos
podemos adicionar uma linha com o item “Outro”.

Com esta tabela definida, nós iremos agora adicionar a coluna TipoPessoa na nossa tabela
Pessoas
. Para este exemplo, vamos apenas definir uma coluna genérica Nome para a nossa
tabela Pessoas:


Listagem 5. Criando a tabela Pessoas com o campo TipoPessoa
create table Pessoas
(
  PessoaID int primary key, 
  TipoPessoaID int references TipoPessoas(TipoPessoaID) not null, 
  Nome varchar(10) not null
)

Sendo assim, nós temos tipos diferentes de Pessoas que estamos modelando, e cada Pessoa
deve ser associada a um tipo.

E agora vem a armadilha: nós iremos adicionar uma coluna extra TipoPessoa para cada uma de
nossas sub-tabelas, e forçar que o valor nesta coluna seja igual ao valor da coluna TipoPessoaID
na linha referente na tabela Pessoas. No SQL 2000, nós podemos simplesmente adicionar um
valor padrão e uma restrição de verificação, mas no SQL 2005 nós podemos usar uma coluna
calculada persistida. Então, nossa tabela Estudantes terá um valor de TipoPessoa constante
igual a 1, a tabela Professores um valor igual a 2 e a tabela Pais um valor igual a 3.

Então, nós simplesmente criamos uma relação de chave estrangeira de volta para a tabela
Pessoas
– mais ao invés de criar a chave apenas na coluna PessoaID, nós criamos a restrição
(chave estrangeira) nas colunas PessoaID e TipoPessoaID. Com essas restrições definidas, nós
garantimos que cada Pessoa terá apenas um tipo e que elas podem ter somente possuir dados
na sub-tabela referente ao seu tipo.

No entanto, existe um porém: nós não podemos criar nossas sub-tabelas dessa forma, usando
chaves estrangeiras; se tentarmos, receberemos a mensagem de erro apresentada na
Listagem 6
.


Listagem 6. Tentativa de criar a tabela Estudantes com chave estrangeira
create table Estudantes 
(
  PessoaID int primary key, 
  TipoPessoaID as 1 persisted, -- estudante
  DataMatricula datetime, 
  foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID)
)
 
Msg 1776, Level 16, State 0, Line 1
Não existem chaves primárias ou candidates na tabela referenciada 'Pessoas' que combine com 
a lista de colunas referenciadas na chave estrangeira 'FK__Students__1F98B2C1'.
Msg 1750, Level 16, State 0, Line 1
A restrição não pôde ser criada. Verifique os erros anteriores.

Isto ocorre porque a restrição de unicidade na tabela Pessoas é apenas na coluna PessoaID, mas
estamos tentando configurar uma restrição de chave estrangeira na combinação das colunas
PessoaID
/TipoPessoaID para uma sub-tabela. Para resolver isso, nós simplesmente adicionamos
uma restrição de unicidade adicional para a tabela Pessoas, cobrindo ambas as colunas PessoaID
e TipoPessoaID (ver Listagem 7).

Listagem 7. Adicionando uma restrição de unicidade na tabela Pessoas
drop table Pessoas
go
 
create table Pessoas
(
  PessoaID int primary key, 
  TipoPessoaID int references TipoPessoa(TipoPessoaID), 
  Nome varchar(10)
  constraint People_AltPK unique (PessoaID,TipoPessoaID)
)

Com isso definido, agora estamos aptos a criar a restrição de chave estrangeira nas sub-tabelas,
como é apresentado na Listagem 8 (ver Nota 1).

Listagem 8. Criando as sub-tabelas com chave estrangeira
create table Estudantes 
(
  PessoaID int primary key, 
  TipoPessoaID as 1 persisted, -- estudante
  DatMatricula datetime, 
  foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID)
)

create table Professores
(
  PessoaID int primary key, 
  TipoPessoaID as 2 persisted, -- professor
  DataAdmissao datetime, 
  foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID)
)

create table Pais
(
  PessoaID int primary key, 
  TipoPessoaID as 3 persisted, -- pais
  GrauDificuldade int,
  foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID)
)

Command(s) completed successfully.


Nota 1. Uso da versão SQL Server 2000

Se você estiver usando o SQL Server 2000, ao invés de usar colunas computadas para as constantes TipoPessoaID, você deveria usar um valor padrão (default) e uma restrição de verificação, pois essa versão anão permite que colunas computadas sejam usadas em restrições de chaves estrangeiras:

 

create table Professores – Versão SQL 2000

(

  PessoaID int primary key,

  TipoPessoaID int not null default 2 check (TipoPessoaID = 2), -- tipo professores

  DataAdmissao datetime,

  foreign key (PessoaID, TipoPessoaID) references Pessoas(PessoaID, TipoPessoaID)

)


Agora iremos testar a solução que demos. Vamos criar uma nova Pessoa, chamada "Claudio", e vamos torná-lo um estudante. Primeiramente, iremos adicionar uma linha à tabela Pessoas, e então uma linha correspondente na tabela Estudantes (ver Listagem 9).

Listagem 9. Realizando Testes – criação de um novo estudante

insert into Pessoas (PessoaID, TipoPessoaID, Nome)
values (1,1,'Claudio')
(1 row(s) affected)
insert into Estudantes (PessoaID, DataMatricula)
values (1,'2007-01-01')
(1 row(s) affected)

Note que nós não podemos adicionar informações de professores a Claudio, pois ele é apenas
um estudante (Listagem 10):

Listagem 10. Realizando Testes – tentativa de adicionar dados de professor a um estudante
insert into Professores (PessoaID, DataAdmissao)
values (1,'2007-01-01')
 
Msg 547, Level 16, State 0, Line 1
A declaração de INSERT conflitou com a restrição de FOREIGN KEY "FK__Teachers__31B762FC". 
O conflito ocorreu no banco de dados "Escola", tabela "dbo.Pessoas".
A declaração foi concluída.

Além disso, é claro que não podemos mudar o tipo de pessoa que é Claudio de estudante para
um professor, pois já existe uma linha na tabela Estudantes para ele (ver Listagem 11).

Listagem 11. Realizando Testes – tentativa de alterar o tipo de uma pessoa
update Pessoas set TipoPessoaID=2 where PessoaID = 1
 
Msg 547, Level 16, State 0, Line 1
A declaração UPDATE conflitou com a restrição de REFERENCE "FK__Students__2645B050". 
O conflito ocorreu no banco de dados "Escola", tabela "dbo.Estudantes".
A declaração foi concluída.

A única forma de mudar o tipo de pessoa que é Claudio é removendo primeiro a linha na tabela
de Estudantes (ver Listagem 12):



Listagem 12. Realizando Testes – alterando o tipo de uma pessoa
-- deletando atributos de estudante:
delete from Estudantes where PessoaID=1
 
-- mudando o tipo de um estudante para um professor
update Pessoas set TipoPessoaID=2 where PessoaID = 1
 
-- adicionando atributos de professor:
insert into Professores (PessoaID, DataAdminissao)
values (1,'2007-01-01')
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

E pronto, conseguimos o que queríamos! Uma restrição "um-para-muitos", onde cada linha em
uma tabela possui uma linha relacionada em somente uma das tabelas possíveis, sem a
necessidade de qualquer trigger ou qualquer programação T-SQL complicada para manter a
integridade dos dados.

Conclusão

Uma vez que tudo foi definido, nós temos um simples modelo de dados com poucas tabelas e
stored procedures, e nós estamos garantindo consistência quando estamos tratando com
atributos e relacionamentos comuns de "Pessoas" no banco de dados. Nós podemos facilmente
criar novos tipos de Pessoas simplesmente através da adição de uma linha na tabela TiposPessoa
e então criar uma nova "sub-tabela". Assim que a nova sub-tabela é criada, este novo tipo
imediatamente possui todos os atributos e relacionamentos de uma Pessoa – sem codificar
qualquer coisa ou fazer quaisquer outras mudanças no banco de dados!

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?