OraFAQ Forum: Server Administration » Problem with foreign key to a unique key

Home » RDBMS Server » Server Administration » Problem with foreign key to a unique key

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with foreign key to a unique key [message #373476]

Wed, 18 April 2001 13:50

Colin
Messages: 6
Registered: April 2001

Junior Member

I posted this message in Oracle Server Board early, but realise that this board is more suitable for it.

My problem can be illustrated by using the following example:

create table orders (
id number primary key,
orderNum vchar2(12),
constraint unq_ord unique (id, orderNum)
)

create table orderitem (
id number primary key,
orderId number,
orderNum vchar2(12),
constraint fk_oi foreign key (orderId, orderNum)
references orders(id, orderNum)
)

You can notice that ORDERITEM references ORDERS using the unique key in that table. The problem is caused by this, but I have no choice in my application.

Ok, the problem is: if I update the ORDERS.orderNum in transaction 1, and I start transaction 2 to insert/delete one row of ORDERITEM, the second process is block until I commit/rollback in transaction 1, even though the update in ORDERITEM has no conflict with that in ORDERS.

If the foreign key is defined to the primary key, there is no such problem. This certainly will be a big problem in production. Anybody knows how to solve it?

Thanks.

Report message to a moderator