Creating a table

[manj]

Hi,

New to Oracle. Currently trying to create a table with Foreign keys but it comes up with an error:

ORDER_ID NUMBER REFERENCES AGGR_BILLING_ITEM (ORDER_ID)

ERROR at line 4:
ORA-02270: no matching unique or primary key for this column-list

Can anybody help? Cheers

 

[Guest_imported]

Er, you should have a Primary Key on the table you're foreign keying into. If it is not unique you should extend the PK to sufficient columns to make it unique. Then you can foreign key in...

 

[karluk]

The error message is saying that you have to create a unique or primary key on the order_id column in table AGGR_BILLING_ITEM. All foreign key definitions must refer to unique values in the other table. Otherwise the database wouldn't know which of many rows in the other table is being referenced.

The way Oracle enforces this need for uniqueness is by insisting on having the primary or unique key properly defined before allowing the creation of the foreign key.

 

[manj]

The thing is that I have created a primary key as follows:

CREATE TABLE AGGR_Billing_Item
(
ORDER_ID NUMBER,
ORDER_ITEM_ID NUMBER,
...
PRIMARY KEY ORDER_ID, ORDER_ITEM_ID)
)
/

 

[stevecal]

Then you have to add order_item_id to your foreign key itself to ensure referential integrity... OR you have to renormalise your table structure so that ORDER_ID is unique in its own right...