foreign key references to a table problem here
create database Exer4
use Exer4
create table customer (
cus_code int,
constraint PK_customer primary key (cus_code),
cus_Lname varchar(20),
cus_Fname varchar (30),
cus_intial varchar (2),
cus_areacode int,
cus_phone int,
cus_balance float
)
create table charter (
char_trip int,
constraint PK_charter primary key (char_trip),
char_date date,
ac_number varchar(5),
foreign key(ac_number) references aircraft,
char_destination varchar(5),
char_distance float,
char_hours_flown float,
char_hours_wait float,
char_fuel_gallons float,
cus_code int,
foreign key(cus_code) references customer
)
create table aircraft(
ac_number varchar(5),
constraint PK_aircraft primary key(ac_number),
mod_code varchar(10),
foreign key(mod_code) references model,
ac_itaf varchar(10),
ac_tiel varchar(10),
ac_开发者_如何转开发tier varchar(10),
)
create table model(
mod_code int,
constraint PK_model primary key(mod_code),
mod_manufacturer varchar(10),
mod_name varchar(10),
mod_seats int,
mod_chg_mile int,
)
create table crew (
char_trip int,
emp_num int,
constraint PK_crew primary key (char_trip,emp_num),
foreign key (char_trip) references charter,
foreign key(emp_num) references employee,
crew_job varchar(10)
)
create table rating (
rtg_code varchar(5),
rtg_name varchar (30),
constraint PK_rating primary key (rtg_code)
)
create table employee (
emp_num int,
constraint PK_employee primary key (emp_num),
emp_title varchar (4),
emp_lname varchar (20),
emp_fname varchar (30),
emp_initial varchar (2),
emp_dob date,
emp_hire_date date,
)
create table pilot (
emp_num int,
pl_license varchar (3),
pl_ratings varchar (30),
pl_med_type int,
pl_med_date date,
pl_pt135_date date,
constraint PK_pilot primary key (emp_num)
)
why is that when i reference to "aircraft" it said invalid table?? what is wrong with my code??
You have to specify which field to reference:
foreign key (mod_code) references model (mod_code),
The foreign key constraint consists of 2 parts:
ALTER TABLE aircraft
ADD CONSTRAINT fk_aircraft_model
FOREIGN KEY (mod_code) -- here you specify the field(s) to reference from in the aircraft table
REFERENCES model (mod_code) -- here you specify the field(s) to reference to in the model table
You are creating the tables in the wrong order. charter
references aircraft
but create table aircraft
doesn't appear until later.
精彩评论