Обсуждение: Strange Error, with unique key
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
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).
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!
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.
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
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!