CODEWITHSUNDEEP
sqldatabaseoracle-databasereferencerecursive-query
specbk
specbk

Reputation: 89

Recursive relationship SQL error

I am pretty new to SQL and I have a problem. I want to make a recursive relationship (a table that relates to itself), but I get an error when I try to execute my code. It's working fine without the Coordinator_Office_ID foreign key.

The error is:

The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.

Create table Logistican (
  Office_ID Number(10) Constraint nb_office Not NULL,
  Worker_ID Number(15) Constraint lg_worker not null,
  Name_logistican Varchar(20),
  Room Varchar(10) constraint log_room UNIQUE,
  Coordinator_Office_ID Integer,
  Primary key (Office_ID, Worker_ID),
  Constraint work_id Foreign key (Worker_ID) References worker(worker_ID) on       delete cascade,
  Constraint lg_cord_id Foreign key (Coordinator_Office_ID) References      Logistican(Office_ID)
);

Upvotes: 1

Views: 717

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Add the constraint with alter table:

Create table Logistican (
    Office_ID Number(10) Constraint nb_office Not NULL,
    Worker_ID Number(15) Constraint lg_worker not null,
    Name_logistican Varchar(20),
    Room Varchar(10) constraint log_room UNIQUE,
    Coordinator_Office_ID Integer,
    Primary key (Office_ID, Worker_ID),
    Constraint work_id Foreign key (Worker_ID) References worker(worker_ID) on delete cascade
);

alter table Logistican
    add Constraint lg_cord_id
        Foreign key (Coordinator_Office_ID, Worker_Id) References      Logistican(Office_ID, Worker_Id);

The relationship needs all elements of the primary key to be valid. I'm not sure if it needs to be a separate statement in Oracle.

Upvotes: 2

Rahul
Rahul

Reputation: 77896

Yes, that's cause you have defined composite primary key like Primary key (Office_ID, Worker_ID) and thus your FK should include both of them else it will result in PFD (partial functional dependency)

Upvotes: 2

Related Questions