Home -> Usenet -> comp.databases.oracle -> Foreign key riddle

Foreign key riddle

From: Martin Haltmayer <mh_at_kirchgruppe.de>
Date: 1996/09/13
Message-ID: <51c1r4$ata_at_brigit.kirchgruppe.de>#1/1 







Hi there,



I sure have a problem with sqlplus. I want to establish a foreign key.
The command file "change-001.sql" reads



alter table SUINVOIC


add constraint FOR_SUINVOIC_SUBSCRIB


foreign key (ACCSUBSCRIBERNR)


references SUBSCRIB (CUCUSTNR) ;



SQL*Plus 3.1.3.7.1 tells me



SQL> _at_change-001


references SUBSCRIB(CUCUSTNR)

                    *




ERROR at line 4: 


ORA-02270: no matching unique or primary key for this column-list 



I wrote a short query "alle-ind.sql" to check if the unique index 
on column cucustnr were present:



undefine 1



select a.index_name, a.uniqueness, b.column_name
from user_indexes a, user_ind_columns b


where a.index_name = b.index_name


and a.table_name = upper ('&&1')


order by a.index_name, b.column_position;



This job will say



SQL> _at_alle-ind


Enter value for 1: subscrib


old   4: and a.table_name = upper ('&&1')
new   4: and a.table_name = upper ('subscrib')


SUBSCRIBKEY001                 UNIQUE    CUCUSTNR
SUBSCRIBKEY002                 UNIQUE    CUREFNUMBER
SUBSCRIBKEY002                 UNIQUE    CUCUSTNR
SUBSCRIBKEY003                 UNIQUE    CUTYPE
SUBSCRIBKEY003                 UNIQUE    CUSTATUS
SUBSCRIBKEY003                 UNIQUE    CUCUSTNR
SUBSCRIBKEY004                 UNIQUE    CULANGUAGE
SUBSCRIBKEY004                 UNIQUE    CUCUSTNR
SUBSCRIBKEY005                 UNIQUE    CUFISCALCODETYPE
SUBSCRIBKEY005                 UNIQUE    CUCUSTNR
SUBSCRIBKEY006                 UNIQUE    CUUNUSED4U7
SUBSCRIBKEY006                 UNIQUE    CUCUSTNR
SUBSCRIBKEY007                 UNIQUE    CUBIRTHDATE
SUBSCRIBKEY007                 UNIQUE    CUCUSTNR


SQL> spool off



As you see, SUBSCRIBKEY001 is a unique index on table SUBSCRIB.
Why does my change-001.sql script not work?



Martin Haltmayer
Received on Fri Sep 13 1996 - 00:00:00 CEST












Original text of this message