Foreign keys: referencing a REFERENCES doesn7t work?

Foreign keys: referencing a REFERENCES doesn7t work?

Post by Jean-Christian Imbeau » Tue, 06 Aug 2002 17:58:52



I have the following schema:

create table PRODUCTS (

id                      char(12)        primary key
);

create table MOVIES (

id                      char(12)        references PRODUCTS,
volume_id               int2            not null default 1,
label_id                integer         references LABELS(id),
length                  int2            not null,

primary key (id, volume_id)
);

create table MOVIE_SERIES (

id                      serial          primary key,
title                   text            not null,
title_kana              text            not null
);

create table REL_MOVIES_SERIES (

prod_id                 char(12)        references MOVIES(id),
series_id               integer         references MOVIE_SERIES(id),

primary key (prod_id, series_id)
);

When trying to enter this SQL I get the following error:

ERROR:  UNIQUE constraint matching given keys for referenced table
"movies" not found

Seems that pgsql is fine when MOVIES.id references PRODUCTS.id for a
foreign key but if a table references MOVIES.prod_id for a foreign key
pgsql cannot go up the reference "tree" and follow what MOVIES.id
references to see that there really is a unique constraint ... hence I
get an error.

Am I missing something or is this a problem with pgsql?

Jc

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Foreign keys: referencing a REFERENCES doesn7t work?

Post by Christian Ullri » Tue, 06 Aug 2002 21:20:14


* Jean-Christian Imbeault wrote on Monday, 2002-08-05:
[I edited out the unnecessary fields]

Quote:> create table PRODUCTS (
> id                      char(12)        primary key
> );

> create table MOVIES (
> id                      char(12)        references PRODUCTS,
> volume_id               int2            not null default 1,
> primary key (id, volume_id)
> );

> create table MOVIE_SERIES (
> id                      serial          primary key
> );

> create table REL_MOVIES_SERIES (
> prod_id                 char(12)        references MOVIES(id),
> series_id               integer         references MOVIE_SERIES(id),
> primary key (prod_id, series_id)
> );

> When trying to enter this SQL I get the following error:

> ERROR:  UNIQUE constraint matching given keys for referenced table
> "movies" not found

> Seems that pgsql is fine when MOVIES.id references PRODUCTS.id for a
> foreign key but if a table references MOVIES.prod_id for a foreign key

                                        ^^^^^^
                                        REL_MOVIES_SERIES?

Quote:> pgsql cannot go up the reference "tree" and follow what MOVIES.id
> references to see that there really is a unique constraint ... hence I
> get an error.

There is _no_ unique constraint on MOVIES.id since it is not declared
UNIQUE and is part of a composite primary key.

--
Christian Ullrich                   Registrierter Linux-User #125183

"Deliver."

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

Foreign keys: referencing a REFERENCES doesn7t work?

Post by Tom La » Tue, 06 Aug 2002 23:15:16



> Seems that pgsql is fine when MOVIES.id references PRODUCTS.id for a
> foreign key but if a table references MOVIES.prod_id for a foreign key
> pgsql cannot go up the reference "tree" and follow what MOVIES.id
> references to see that there really is a unique constraint ...

No, there isn't a unique constraint.  Your REFERENCES clause says that
every ID in MOVIES must equal some ID in PRODUCTS; it does *not* say
that two different rows in MOVIES can't reference the same ID in
PRODUCTS.  Add a UNIQUE constraint to MOVIES if that's the behavior you
want.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

1. Foreign Key references non-primary key field?

Is there any way to create a foreign key that references a field which
is NOT a primary key?  I'm running into the error exemplified below.

** Error Example **
CREATE TABLE  AppLog(
aLogID  Number(7) NOT NULL,
IUserID  Number(7) NOT NULL,
CONSTRAINT pk_AppLog PRIMARY KEY (aLogID));

CREATE TABLE  Employees(
szLogin Varchar2(32) NOT NULL,
aEmpID  Number(7) NOT NULL,
CONSTRAINT pk_Employees PRIMARY KEY(szLogin));

CREATE UNIQUE INDEX Employees_Index_1
ON Employees (aEmpID);

ALTER TABLE Applog
ADD CONSTRAINT fk_Employees
FOREIGN KEY (IUserID) REFERENCES Employees (aEmpID);
** End Example**

This last SQL statement generates the following error:
ORA-02270: no matching unique or primary key for this column-list

This situation has cropped up because I am replicating an Access DB
which contains multiple foreign key references to non-primary key
fields.
Altering the DB table structures would be dangerous.

Thanks for your time!
-Andrew Duryea
Bayer, Pitts IS

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Where to check the connection info

3. Foreign key references to non-primary key columns

4. Problem with backup when we use HP Omniback and SQL

5. Foreign Key and References

6. Using Crystal Reports with Oracle Forms 4.5 - a solution

7. the replicated table is "referenced by a FOREIGN KEY constraint

8. FOR XML AUTO, ELEMENTS

9. foreign key referencing to more than columns in the same table

10. creating a foreign key reference to sysobjects

11. Cross-database foreign key references

12. FOREIGN KEY x REFERENCES sysobjects(id) ?

13. Cross Database Foreign Key Reference