Re: Error enabling FK constraint

Foreign key should include and reference all the columns in referenced primary key.
In you case:

alter table TB_DEPT add foreign key (COMPANYID, STORELOCID) references tb_stores(COMPANYID, STORELOCID);

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman_at_perceptron.com

• Original Message ----- From: "Helmut Daiminger" <hdaiminger_at_vivonet.com> To: "Oracle List (Telelist)" <ORACLE-L_at_fatcity.com>; "Oracle DBA List (Lazy DBA)" <oracledba_at_quickdoc.co.uk> Sent: Wednesday, September 20, 2000 12:57 PM 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
>
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com
(updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
Received on Wed Sep 20 2000 - 12:14:36 CDT