No matching unique or primary key for this column-list (Trying to add two FK's )

Question

I'm getting the error no matching unique or primary key for this column list, what could be? I'm using oracle live sql. The table who is getting the error is the last one, the others are working ok. The error is ORA 02270

CREATE TABLE flightBooking
( bookingCode varchar(5) not null, 
  stretchSet varchar(50),
  expireDate varchar(12),
  CONSTRAINT flightBooking_pk PRIMARY KEY (bookingCode)
); 

CREATE TABLE customer
( cpf varchar(10),
  rg varchar(7),
  personName varchar(30),
  birth varchar(12),
  email varchar(30),
  city varchar(30),
  uf varchar(2),
  bookingCode varchar(5) not null,
  CONSTRAINT fk_bookingCode FOREIGN KEY (bookingCode) REFERENCES flightBooking(bookingCode)
);  

CREATE TABLE stretch
( flightDate varchar(12),
  flightHour varchar(6),
  flightClass varchar(15),
  flightCode varchar(5) not null,
  destination varchar(30),
  origin varchar(30),
  scale varchar(60),
  aeroplaneType varchar(30)
);

CREATE TABLE sell
(voucher varchar(50),
 bookingCode varchar(5) not null,
 flightCode varchar(5) not null,
 CONSTRAINT fk_bookingCode FOREIGN KEY (bookingCode) REFERENCES flightBooking(bookingCode),
 CONSTRAINT fk_flightCode FOREIGN KEY (flightCode) REFERENCES stretch(flightCode)

);````

score 0 · Answer 1 · answered Jul 11 '20 at 14:16

Table you're referencing must have - as the error message says - unique or primary key.

You're referencing STRETCH table and its FLIGHTCODE column, but there's no unique nor primary key on it. I suggest you create it.

A side note: use varchar2, not varchar.

sticky bit · Accepted Answer · 2020-07-11T14:22:43.603

A foreign key must point to a column (or tuple of columns) that are a key, i.e. either declared a primary key or at least unique.

Presumably you forgot the primary key constraint in strech on flightcode.

CREATE TABLE stretch
             (...
              PRIMARY KEY (flightcode));

And as a side note: stretchset in flightbooking looks suspicious. If you intend to store a comma (or any other character) separated list of flightcodes from strech in it, don't. Instead read "Is storing a delimited list in a database column really that bad?" and normalize the schema by using another table linking bookings and stretches.

And another side note: bookingcode probably shouldn't be in customer as this would mean that one customer can only ever do one booking. Consider a foreign key to a customer in the booking table.

And yet another one: Use appropriate data types. Dates/times like flightdate (and flighthour, which should really be included in flightdate) in stretch, birth in customer or expiredate in flightbooking shouldn't be strings. Use a date/time type instead.

No matching unique or primary key for this column-list (Trying to add two FK's )

2 Answers2