Foreign Key Reference

Question

I addressed in class that the 2 foreign keys in the FlightData table are Depart_Code and Ariv_Code that there isn't any table to make references to them being a primary key in, in the relational schema we were given.

In class I was told that they reference Airport_Code in the Airport table. I was wondering I would go about doing that? I feel like I am missing something obvious. I appreciate any help offered I am still new to database in general and I am currently on Oracle 11g.

Airport table

CREATE TABLE Airport
(
     Airport_Code VARCHAR2(7) CONSTRAINT pk_Airport Primary Key,
     City_Code VARCHAR2(3), 
     CONSTRAINT fk_Airport_City_Code 
         FOREIGN KEY(City_Code) REFERENCES City, 
     Airport_Name VARCHAR2(30)
);

FlightData table:

CREATE TABLE FlightData
(
    Flt_Nbr VARCHAR2(3) CONSTRAINT pk_FlightData Primary Key,
    Depart_Code VARCHAR2(30), 
    Ariv_Code VARCHAR2(30)
);

Answer 1

To make sure Depart_Code and Ariv_Code always reference an airport in the Airport table you need to:

• Make these columns NOT NULL.
• Ensure they have the same data type as the key in Airport. Make them have a length of 7.
• Add two foreign key constraints, each one based on each column.

For example, the second table could look like:

CREATE TABLE FlightData (
  Flt_Nbr VARCHAR2(3) CONSTRAINT pk_FlightData Primary Key,
  Depart_Code VARCHAR2(7) not null,
  constraint fk1 foreign key (Depart_Code) references Airport (Airport_Code),
  Ariv_Code VARCHAR2(7) not null,
  constraint fk2 foreign key (Ariv_Code) references Airport (Airport_Code)
);