Re: Strange Error, with unique key

On Wed, 9 Apr 2003 john(dot)murdoch(at)ig(dot)com(dot)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_responsvel 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).