Error adding ForeignKey in Oracle

Question

Error adding ForeignKey in Oracle

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

create table LUGAR_PERSONA
(
  lugar_id    NUMBER(8) not null,
  personal_id NUMBER(8) not null
)
nologging;
alter table LUGAR_PERSONA
  add constraint FK_lugar FOREIGN KEY (lugar_id) REFERENCES lugar(lugar_id);
  add constraint FK_personal FOREIGN KEY (personal_id) REFERENCES personal(personal_id);

I create a table LUGAR_PERSONA, that must be an intermediate table to associate one or several places with one or several people. Obviously I have created the personal table and the table place.

Trying to add the FOREIGN KEY gives me this error.

alter table PLACE_PERSON * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

What can this error be caused by?

PS: I add the structure of the parent tables and I do it as an image because it's only to show that part is okay.

sql oracle

asked by Alexis Granja 02.02.2017 в 18:17

source

2 answers

0

CREATE TABLE table_name ( column1 datatype null / not null, column2 datatype null / not null, ...

CONSTRAINT fk_column FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n) );

Leave everything inside the parenthesis of the create table. In passing I see that it is missing; in create statement.

answered by 02.02.2017 в 18:29

Why can not I see the sys objects? of SQL Server? AdMob missin adSize on Android

score 1 · Accepted Answer

You have 2 problems. The first is that you have a semicolon left over at the end of this line:

add constraint FK_lugar FOREIGN KEY (lugar_id) REFERENCES lugar(lugar_id);

The complete sentence should be:

alter table LUGAR_PERSONA
  add constraint FK_lugar FOREIGN KEY (lugar_id) REFERENCES lugar(lugar_id) -- sin punto y coma aquí
  add constraint FK_personal FOREIGN KEY (personal_id) REFERENCES personal(personal_id);

But that is not the cause of your error. But I mention it, because once you solve your current error, then you will receive a different one because of the semicolon.

The error you receive is because Oracle is not able to acquire a lock for one of the tables wrapped in alter table . In this case, it is most likely that the problem is with the lugar table.

If you verify, you will surely find that someone (maybe yourself in a different window) has an open transaction with modifications to the lugar table. And until a commit or rollback is made to that transaction, the alter table will continue to give that error.