Creating Foreign key error
Hi,
I'm trying to refers two columns of this table
create table passenger (
PNR_NO char (8) primary key,
FLIGHT_NO char(5) constraint fk_flight foreign key references flight (FLIGHT_NO) not null,
TRAVEL_DATE date constraint fk_travel_date foreign key references flight (flight_date) not null,
FIRST_NAME varchar(20) not null,
LAST_NAME varchar(20) not null,
AGE int not null,
GENDER char(1) not null,
CLASS char(1) not null,
MEAL_PREF char(1) not null,
SSR varchar(150) not null,
STATUSS char (1),
CANCEL_FLAG char(1) not null,
PHOTO image not null
)
with two of a composite key fails
create table flight(
FLIGHT_NO char(5),
FLIGHT_DATE date,
DEP_TIME time not null,
ARR_TIME time not null,
AIRCRAFT_ID char (4) constraint fk Foreign key references aircraft (AIRCRAFT_ID) not null,
FIRST_CLASS_SEATS int not null,
BUSINESS_CLASS_SEATS int not null,
ECO_CLASS_SEATS int not null,
constraint pk primary key (FLIGHT_NO, FLIGHT_DATE)
)
i'm getting this ERROR :
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'flight' that match the referencing column list in the foreign key 'fk_flight'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

mayank goyelPosted Sep 22, 2009, 1:30 AM
You need either primary key or candidate key to map for a foreign key. In your case, you need to make foreign keys from same table. Therefore, you can not create multiple primary/candiate keys in the same table.
To overcome this issue, use Unique index or Unique constraint on Flight No and Date fields in flight table.
Note: Please accept the answer if it solves your problem.