Creating Foreign key error

Question

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 goyel · Answer

Hi 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.