Thread: Strange Error, with unique key

Strange Error, with unique key

From
john.murdoch@ig.com.br
Date:
I pasted here, from the begining of the database creation for explain it:

[xpy@furtab xpy]$ dropdb laboratorio
DROP DATABASE
[xpy@furtab xpy]$ createdb laboratorio
CREATE DATABASE
[xpy@furtab xpy]$ psql laboratorio
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

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

_________________________________________________________
Voce quer um iGMail protegido contra v�rus e spams?
Clique aqui: http://www.igmailseguro.ig.com.br


Re: Strange Error, with unique key

From
Stephan Szabo
Date:
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).


multiple fields index

From
"enediel"
Date:
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!


Re: multiple fields index

From
Stephan Szabo
Date:
On Wed, 9 Apr 2003, 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.

You'll need to generate a simple function that takes an int and a text and
generates a unique value based on those that you use in the index and
queries.  If there's a known value that won't occur in client name this
could be as simple as concatenating nu_intitution and upper(client_name)
with a separator.


Re: multiple fields index

From
Richard Huxton
Date:
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


Re: multiple fields index

From
"enediel"
Date:
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!