Message-Id: <10625.117508@fatcity.com> From: Tom Tyson Date: Wed, 20 Sep 2000 15:06:26 -0700 (PDT) Subject: Re: Error enabling FK constraint 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 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@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@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!?