Re: Foreign key riddle

Martin Haltmayer wrote:
>
> 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

-- 

In order to create a FOREIGN KEY CONSTRAINT on SUINVOIC(ACCSUBSCRIBERNR)
referencing column CUCUSTNR of table SUBSCRIB, you need to have created
a UNIQUE or PRIMARY KEY "CONSTRAINT" on SUBSCRIB for the column
CUCUSTNR.
Apparently, you have created a UNIQUE index on CUCUSTNR using the CREATE
UNIQUE INDEX command. This will not let you create referential integrity
constraints referencing this column. 

The best thing to do will be to drop your UNIQUE index and :-



> ALTER TABLE SUBSCRIB ADD CONSTRAINT PK_SUBSCRIB PRIMARY (CUCUSTNR); or


> ALTER TABLE SUBSCRIB ADD CONSTRAINT UK_SUBSCRIB UNIQUE (CUCUSTNR);



This will also create a UNIQUE index for you. Infact ORACLE's Server
Administrator's Guide recommends that PRIMARY KEY constraints or UNIQUE
KEY constraints should be created whenever possible, as opposed to using
the almost obsolete CREATE UNIQUE INDEX command. 


-----------------------------------------------------------------
Naresh Ramamurti 			nramamur_at_worldnet.att.net
-----------------------------------------------------------------