| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Error enabling FK constraint
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 Liststo: 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
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |