Strange Error, with unique key

On Wed, 9 Apr 2003 john.murdoch@ig.com.br wrote:

laboratorio=> CREATE TABLE PESSOA_RESPONSAVEL
laboratorio-> (
laboratorio(> PESSOA INTEGER NOT NULL,
laboratorio(> CODIGO_CONSELHO VARCHAR,
laboratorio(> ESPECIALIDADE INTEGER NOT NULL,
laboratorio(> PRIMARY KEY (PESSOA, ESPECIALIDADE)
laboratorio(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'pessoa_responsav
el_pkey' for table 'pessoa_responsavel'
CREATE
laboratorio=> CREATE TABLE RESULTADO_EXAME
laboratorio-> (
laboratorio(> NR_PEDIDO INTEGER NOT NULL,
laboratorio(> EXAME INTEGER NOT NULL,
laboratorio(> SEQUENCIA SMALLINT NOT NULL,
laboratorio(> RESULTADO MONEY,
laboratorio(> PESSOA INTEGER NOT NULL,
laboratorio(> PRIMARY KEY (NR_PEDIDO, EXAME, SEQUENCIA)
laboratorio(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'resultado_exame_
pkey' for table 'resultado_exame'
CREATE
laboratorio=> ALTER TABLE RESULTADO_EXAME ADD FOREIGN KEY (PESSOA)
REFERENCES PE
SSOA_RESPONSAVEL (PESSOA);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR: UNIQUE constraint matching given keys for referenced table
"pessoa_respo
nsavel" not found
laboratorio=>

Anyone knows what is this error about?
If I am right this pessoa_respons�vel is already a unique key (pk).

It's not AFAICS, it's unique when combined with ESPECIALIDADE but not
necessarily by itself. The set of columns for the foreign key target must
match the set of keys in the primary key (or unique constraint).

On Wednesday 09 Apr 2003 6:09 pm, enediel wrote:

I need a database where all text information will be kept as the users
wrote it.

This purposes includes a problem to create unique indexes with multiples
fields, where text fields are part of the indexes.

Suppose for example a table INSTITUTIONS, and other table
CLIENTS_PER_INSTITUTION
I need to create a unique index (nu_intitution, client_name) in the second
table where nu_intitution is an integer and a foreign key of the first
table, and client_name is a text.

Someting like
create index i_clients on CLIENTS_PER_INSTITUTION (nu_intitution,
upper(client_name)) is impossible, at least in the documentation I've read.

Well, the only issue would be with using the function in the index. You
shouldn't have a problem with (nu_intitution, client_name).

If you want to upper() or lower() the client_name, I think you'll have to
define a custom function and index on that:

CREATE INDEX i_clients on CLIENTS_PER_INSTITUTION
( my_custom_func(nu_intitution, client_name) );

Concatenate the number and lower(...) inside the custom function and return
it. There's a short section on functional indexes in the manuals.
--
Richard Huxton

I need a database where all text information will be kept as the users wrote
it.

This purposes includes a problem to create unique indexes with multiples
fields, where text fields are part of the indexes.

Suppose for example a table INSTITUTIONS, and other table
CLIENTS_PER_INSTITUTION
I need to create a unique index (nu_intitution, client_name) in the second
table where nu_intitution is an integer and a foreign key of the first
table, and client_name is a text.

Someting like
create index i_clients on CLIENTS_PER_INSTITUTION (nu_intitution,
upper(client_name)) is impossible, at least in the documentation I've read.

It's just an example, but I have the similar case in many tables.

Thanks in advance for any help to solve this problem

Enediel
Linux user 300141

Happy who can penetrate the secret causes of the things
¡Use Linux!

Thanks for all answers, you have given me the way to solve definitely this
question.

Greetings
Enediel
Linux user 300141

Happy who can penetrate the secret causes of the things
¡Use Linux!