Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: Error after error

    CREATE TABLE Leg_instance
    (flight_no varchar2(10),
    leg_no varchar2(15),
    leg_date varchar2(15),
    number_available_seats varchar2(15),
    Airplane_id varchar2(5),
    departure_airport_code varchar2(15),
    departure_time varchar2(5),
    arrival_airport_code varchar(15),
    arrival_time varchar(5),
    CONSTRAINT leg_date_pk PRIMARY KEY (leg_date, leg_no, flight_no),
    CONSTRAINT flight_no_fk4 FOREIGN KEY (flight_no) REFERENCES flight (flight_no),
    CONSTRAINT departure_airport_code_fk2 FOREIGN KEY (departure_airport_code) REFERENCES Flight_Leg (departure_airport_code),
    CONSTRAINT arrival_airport_code_fk2 FOREIGN KEY (arrival_airport_code) REFERENCES Flight_leg (arrival_airport_code),
    CONSTRAINT airplane_id_fk FOREIGN KEY (airplane_id) REFERENCES airplane (airplane_id),
    CONSTRAINT leg_no_fk2 FOREIGN KEY (leg_no) REFERENCES Flight_leg (leg_no));
    I keep getting an ORA-02270: no matching unique or primary key for this column-list

    This worked fine before i added a foreign key too departure_airport_code, arrival_airport_code and leg_no.

    I had to delete the table to add these in.

    If anyone could help that would be much appreciated thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A table you are referencing must have unique or primary key.

    For example: table FLIGHT must have uniquer or primary key on FLIGHT_NO column.

    The same goes for FLIGHT_LEG table (and columns you are referencing).

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    Ah right i see, so departure_airport_code and arrival airport_code cannot be used as foreign keys because they are not primary keys in another table?

    I took them out and it worked.

    Thanks for the help. but does that mean i have to make they primary keys in the other table for it to work.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > does that mean i have to make they primary keys in the other table for it to work.

    Exact solution depends upon precisely what you mean by "work".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The parent file must have a primary or unique index on the column being used for the foreign key.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example; I'll try to simulate your example (partially, at least), so that you could follow it easier.

    First, I'll create two tables that are referenced by the third one:
    Code:
    SQL> create table flight
      2    (flight_no              varchar2(10) primary key);
    
    Table created.
    
    SQL> create table flight_leg
      2    (flight_no              varchar2(15),
      3     departure_airport_code varchar2(15),
      4     arrival_airport_code   varchar2(15),
      5     some_column            number
      6    );
    
    Table created.
    
    SQL>
    Now, the third (child) table. I'll reference only the FLIGHT table, which does have a primary key:
    Code:
    SQL> create table leg_instance
      2    (flight_no              varchar2(10),
      3     leg_no                 varchar2(15),
      4     airplane_id            varchar2(5),
      5     departure_airport_code varchar2(15),
      6     arrival_airport_code   varchar2(15),
      7            constraint li_pk primary key (leg_no),
      8            constraint li_fk_fl
      9              foreign key (flight_no)
     10              references flight (flight_no)
     11  );
    
    Table created.
    
    SQL>
    Success!

    Now let's add reference to FLIGHT_LEG table, which does not have a primary key:
    Code:
    SQL> drop table leg_instance;
    
    Table dropped.
    
    SQL> create table leg_instance
      2    (flight_no              varchar2(10),
      3     leg_no                 varchar2(15),
      4     airplane_id            varchar2(5),
      5     departure_airport_code varchar2(15),
      6     arrival_airport_code   varchar2(15),
      7            constraint li_pk primary key (leg_no),
      8            constraint li_fk_fl
      9              foreign key (flight_no)
     10              references flight (flight_no),
     11            constraint li_fl_fk
     12              foreign key (flight_no, departure_airport_code, arrival_airport_code)
     13              references flight_leg (flight_no, departure_airport_code, arrival_airport_code)
     14    );
                references flight_leg (flight_no, departure_airport_code, arrival_airport_code)
                                       *
    ERROR at line 13:
    ORA-02270: no matching unique or primary key for this column-list
    
    
    SQL>
    It won't work, as expected. So let's add a primary key to the FLIGHT_LEG table and try again:
    Code:
    SQL> alter table flight_leg add constraint fl_pk primary key
      2    (flight_no, departure_airport_code, arrival_airport_code);
    
    Table altered.
    
    SQL> create table leg_instance
      2    (flight_no              varchar2(10),
      3     leg_no                 varchar2(15),
      4     airplane_id            varchar2(5),
      5     departure_airport_code varchar2(15),
      6     arrival_airport_code   varchar2(15),
      7            constraint li_pk primary key (leg_no),
      8            constraint li_fk_fl
      9              foreign key (flight_no)
     10              references flight (flight_no),
     11            constraint li_fl_fk
     12              foreign key (flight_no, departure_airport_code, arrival_airport_code)
     13              references flight_leg (flight_no, departure_airport_code, arrival_airport_code)
     14    );
    
    Table created.
    
    SQL>
    Success!

    For illustration, let's see that unique key serves as good as primary key; first we have to drop constraints, then we'll create them:
    Code:
    SQL> alter table leg_instance drop constraint li_fl_fk;
    
    Table altered.
    
    SQL> alter table flight_leg drop constraint fl_pk;
    
    Table altered.
    
    SQL> -- note the UNIQUE keyword!
    SQL> alter table flight_leg add constraint fl_uk unique
      2    (flight_no, departure_airport_code, arrival_airport_code);
    
    Table altered.
    
    SQL> alter table leg_instance add constraint li_fl_fk
      2    foreign key (flight_no, departure_airport_code, arrival_airport_code)
      3    references flight_leg (flight_no, departure_airport_code, arrival_airport_code);
    
    Table altered.
    
    SQL>
    Works just fine!


    Finally: I believe Bill meant it right but put it wrong: unique index can't fit in here (as opposed to unique/primary key, as presented earlier):
    Code:
    SQL> alter table leg_instance drop constraint li_fl_fk;
    
    Table altered.
    
    SQL> alter table flight_leg drop constraint fl_uk;
    
    Table altered.
    
    SQL> -- creating unique INDEX (which is different from KEY!)
    SQL> create unique index ui_fl on flight_leg
      2    (flight_no, departure_airport_code, arrival_airport_code);
    
    Index created.
    
    SQL> alter table leg_instance add constraint li_fl_fk
      2    foreign key (flight_no, departure_airport_code, arrival_airport_code)
      3    references flight_leg (flight_no, departure_airport_code, arrival_airport_code);
      references flight_leg (flight_no, departure_airport_code, arrival_airport_code)
                             *
    ERROR at line 3:
    ORA-02270: no matching unique or primary key for this column-list
    
    
    SQL>
    I guess that's all, more or less.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •