Problemas com Constraint Check

Assuntos Gerais
#1

Olá pessoal,

voltei aqui depois de muito tempo e com uma questão que está me ‘apurrinhando’ a vida.

Estou desenvolvendo uma aplicação web com banco de dados PostgreSQL 9.1 e usando o Hibernate 3.

Para checar se um registro já está cadastrado (ex. já existe uma cidade para um estado)tive que fazer uma função e então chamar a função na Constraint Check.

Porém ao tentar executar um update ou insert a constraint não valida o check.

Alguém poderia me ajudar?

Segue o script

/**
 * Validacao de Cidade Duplicada.
 */
CREATE OR REPLACE FUNCTION chk_cidade( estado_id bigint, nome_cid varchar(255))
  RETURNS integer AS
$BODY$
DECLARE
	retval int;
BEGIN
	SELECT INTO retval(select count(*) from public.cidade
	where ID_ESTADO = estado_id
	and lower(rtrim(ltrim(nome))) like lower(rtrim(ltrim(nome_cid)))
	);
	return retval;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

ALTER TABLE Cidade ADD Constraint chk_cidade_duplicada 
CHECK ( chk_cidade(id_estado, nome) < 2 );

Desde já muito obrigado pela ajuda

#2

Alguém para ajudar? Que já passou por algo parecido, ou tem outra forma de fazer?

#3

Acho que no seu caso seria mais simples usar uma constraint Unique

http://www.postgresql.org/docs/8.1/static/ddl-constraints.html

#4

Tentei usar unique, porém, o postgres é case sensitive, ou seja, difere maiúscula de minúscula, e não revolve meu problema =(

Pois para a minha aplicação: Sao Paulo é igual SAO PAULO e toda o qualquer variação.

Mas agradeço pela ajuda.

#5

[quote=hbrayres]Tentei usar unique, porém, o postgres é case sensitive, ou seja, difere maiúscula de minúscula, e não revolve meu problema =(

Pois para a minha aplicação: Sao Paulo é igual SAO PAULO e toda o qualquer variação.
[/quote]

Hum, utilize um índice único então. Nele você pode usar o resultado de uma expressão assim com queria na sua função.

De qualquer forma, acho que mesmo isso não resolve completamente seu problema.

Repare que São Paulo e Sao Paulo ainda serão cidades diferentes.
Não sei se é possível configurar a collation do banco para considerar uma letra acentuada igual a uma sem acento.

Outra coisa é que se isso ficar na mão do usuário, logo logo terá “sao paulo”, “s paulo”, “saopaulo” “sp” e por aí vai.

No caso de cidades, o ideal seria utilizar aquela base de ceps dos correios. Já ajuda o usuário a preencher.

Se não for possível, recomendo normalizar direto na aplicação, antes de chegar ao banco de dados.

#6

[quote] AbelBueno worte:
Hum, utilize um índice único então. Nele você pode usar o resultado de uma expressão assim com queria na sua função.
[/quote]

AbelBueno,

realmente conseguir fazer usando UNIQUE INDEX, mas ainda sim continuo sem entender o por que a CONSTRAINT usando uma chamada de função não funcionou.

Para solução do problema com UNIQUE usei a seguinte URL:

http://www.postgresql.org/docs/8.2/static/sql-createindex.html

#7

Usei o seguinte script para resolver o problema:

CREATE UNIQUE INDEX UK_CIDADE ON CIDADE ( (lower(NOME)), id_estado );

#8

Esqueci de comentar isso.

Na verdade, a chamada de função funciona, mas tinha um erro na sua lógica.

O check é testado ANTES de inserir o registro.
Portanto a quantidade de cidades deveria ser 0 ( ou < 1) e não < 2, como colocou.

Do jeito que está, você permitiria dois registros para cada nome de cidade.

#9

Entao a lógica está certa, pois fiz alguns testes.

Exemplo:

  1. Ao criar a função executei um select, na funcao criada, que me retornou, logicamente o valor do objetos duplicados, para o caso ideal retornou ‘1’
  2. Associar via CHECK CONSTRAINT a função, sem problemas.
    O Problema…
  3. Fazer DROP do CHECK e criar um registro duplicado.
  4. Select na função criada, verificar que está maior que ‘1’.
  5. Tentar fazer a associação da função ao CHECK CONSTRAINT (com verificador < 2, pois se colocar < 1 dará erro pois no banco já existe registros), verificar o erro que dará.
#10

Do jeito que está a função, o resultado dela varia se é antes de uma inclusão ou se é para adicionar a constraint com a tabela preenchida.

Para funcionar assim, você poderia editar a função para contar se existe aquele nome para uma primary key diferente da do registro.

Criando a função assim, você poderia definir a constraint como:

  CHECK ( ch_duplicada(id, estado_id, nome) = 0 )

Funcionaria nos dois momentos.
Mas falo apenas por ser possível, o índice único é uma solução bem melhor para isso.

#11

Entendi, AbelBueno.

Valeu pela dica.

#12