Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Error enabling FK constraint

RE: Error enabling FK constraint

From: Kevin Martin <kevin.martin_at_catapultsystems.com>
Date: Thu, 21 Sep 2000 09:39:42 -0500
Message-Id: <10626.117577@fatcity.com>


The FK on tbl_dept must reference either a PK or UNIQUE constraint on the parent table (tb_stores). In this case, tb_stores.storelocid is part of a compound PK, but does not have its own unique constraint.

So, it seems that you have two options
1. Create a unique constraint on tb_stores.storelocid (if values for storelocid support this)
2. Change the FK to reference both columns of the PK on tb_stores

Good Luck.
-km

-----Original Message-----

From: Helmut Daiminger [mailto:hdaiminger_at_vivonet.com] Sent: Wednesday, September 20, 2000 1:01 PM To: Multiple recipients of list ORACLE-L Subject: Error enabling FK constraint

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 Received on Thu Sep 21 2000 - 09:39:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US