I have a problem with foreign key c

[ija]

I have a problem with foreign key contraint. Please have a look at the following log. It creates two tables and adds a foreign key constraint to the second one.
[tt]
-- create parent table
CREATE TABLE PARENT_TABLE (
AP_ID VARCHAR2 (10) NOT NULL,
OWNER_ID NUMBER (9) )

Table created

CREATE UNIQUE INDEX PARENT_TABLE_U1 ON
PARENT_TABLE(AP_ID, OWNER_ID)

Index created

-- create child table
CREATE TABLE CHILD_TABLE (
AP_ID VARCHAR2 (10) NOT NULL,
OWNER_ID NUMBER (9))

Table created

-- add foreign key constraint
ALTER TABLE KONVERZE.CHILD_TABLE
ADD CONSTRAINT CHILD_TABLE_FK1
FOREIGN KEY (ap_id, OWNER_ID)
REFERENCES PARENT_TABLE (ap_id, OWNER_ID)
EXCEPTIONS INTO CRM_EXCEPTION

ORA-02270: no matching unique or primary key for this column-list
[tt][/color]

What is wrong? I cannot create (ap_id, owner_id) as a primary key, because that has to be other...

 

[karluk]

Oracle needs a unique constraint. One would think that a unique index on the parent table would serve your purposes, but is not what Oracle is looking for.

To fix the problem drop the unique index and create a unique constraint on the same columns. Then your foreign key creation will work:

drop index parent_table_u1;
alter table parent_table
add constraint parent_table_u1
unique (ap_id, owner_id);

 

[ija]

Thanks. This works...