Helmut
Before this question is answered, shouldn't the foreign key contain both the
companyid and the storelocid, since these are both part of the primary key.
Otherwise, you cannot determine the correct storelocid between companylocid's.
I might be reading too much into this.. but going on my assumption you would do
the following query to get a list of all the rows that don't have a primary key
matching with both a companyid and a storelocid from the tb_dept table to the
tb_stores table:
SELECT b.companyid, b.storelocid
FROM tb_dept a, tb_stores b
WHERE a.companyid = b.companyid (+) AND
a.storelocid = b.storelocid (+) AND
(b.companyid IS NULL OR
b.storelocid IS NULL)
( I believe this SQL is right.. typed it up without testing it :) )
If you really wanted the foriegn key to be as you stated, I would have run the
following query:
SELECT b.storelocid
FROM tb_dept a, tb_stores b
WHERE a.storelocid = b.storelocid (+) AND
b.storelocid IS NULL)
Hope that helps
Tom Tyson
- Helmut Daiminger <hdaiminger_at_vivonet.com> wrote:
> Hi!
>
> I just started with a new company and was asked to have a look at our
> database and implement some more PK - FK constraints, since not all of them
> were implemented from the beginning... No comment...
>
> Anyway, if I want to enable e.g. the following constraint
>
> SQLWKS> alter table TB_DEPT add foreign key (STORELOCID) references
> tb_stores(STORELOCID);
>
> I get the follwing error message: ORA-02270: no matching unique or primary
> key for this column-list
>
> What can I do about this? The two tables look like:
>
> TB_DEPT: Pimary Key: companyid, storelocid, deptid
>
> SQLWKS> desc tb_dept
> Column Name Null? Type
> ------------------------------ -------- ----
> COMPANYID (= PK) NOT NULL NUMBER
> STORELOCID (= PK) NOT NULL NUMBER
> DEPTID (= PK) NOT NULL NUMBER
> DESCRIPTION NOT NULL VARCHAR2(50)
> PRNPRI NOT NULL NUMBER
> INVTYPEID NUMBER
> SALESGRPID NUMBER
> TAXGRPID NUMBER
> ACCOUNTID NUMBER
> STATUS NOT NULL CHAR(2)
>
>
>
> TB_STORES: Primary Key: companyid, storelocid
>
> SQLWKS> desc tb_stores
> Column Name Null? Type
> ------------------------------ -------- ----
> COMPANYID (= PK) NOT NULL NUMBER
> STORELOCID (= PK) NOT NULL NUMBER
> DESCRIPTION NOT NULL VARCHAR2(50)
> TIMEZONEID NOT NULL NUMBER
> STATUS NOT NULL CHAR(2)
> ENDDAY NOT NULL DATE
>
>
> Any ideas?
>
> Thanks,
> Helmut
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Helmut Daiminger
> INET: hdaiminger_at_vivonet.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Do You Yahoo!?
Received on Wed Sep 20 2000 - 17:06:26 CDT
