create database HorizonAirways
create table Sector
(
SectorID Char(5) constraint SectorID primary key clustered not null,
Description VarChar(50) not null,
WeekDay1 char(3) not null,
WeekDay2 char(3) not null,
FirstClassFare Money not null,
BusinessClassFare Money not null,
EconomyClassFare Money not null
)
select * from Sector
create table Aircraft
(
AircraftTypeID char(4) constraint AircraftTypeID primary key clustered not null,
Description char(30) not null,
FirstClassSeats int not null,
BusinessClassSeats int not null,
EconomyClassSeats int not null
)
select * from Aircraft
create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)
select * from Flights
create table ScheduledFlights
(
FlightNo char(5) references flights(FlightNo) not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)
select * from ScheduledFlights
create table passenger
(
PnrNo char(8) constraint PNR primary key clustered not null,
FlightNo char(5) references flights(FlightNo) not null,
TravelDate datetime constraint PassengerFlights Foreign key (FlightNo, DeptTime) references flights (FlightNo,DepTime) not null,
FName char(20) not null,
LName char(20) not null,
Age int not null,
Gender char(1) not null,
Class char(15) not null,
SeatPref char(6) not null,
MealPref char(15) not null,
SSR varchar(100) not null,
Status char(15)
)
create table DailyCollection
(
PnrNo char(8) references Passenger(PnrNo) not null,
TransDate Datetime not null,
TranType Char(1) not null,
Amount Float not null
)
create table users
(
UserName char(15) constraint Username primary key clustered not null,
Password char(15) not null,
UserRole Char(15) not null
)
!!OUTPUT!!
--
Msg 8140, Level 16, State 0, Line 48
More than one key specified in column level FOREIGN KEY constraint, table 'passenger'.
Msg 1769, Level 16, State 1, Line 48
Foreign key 'PassengerFlights' references invalid column 'DeptTime' in referencing table 'passenger'.
Msg 1750, Level 16, State 0, Line 48
Could not create constraint. See previous errors.
--
--
In the project given by our teacher it says about Passenger Table
Pnr(PK)- Char (8) no null values
FlightNo- Char(5) no null values
TravelDate- DateTime no null values
info-Date of travel. The flight number and the date of travel together gorm a foreign key that references the flight number and the flight date in theFlight Table.
--
rest is fine.....
CAN ANYONE PLEASE RECTIFY THESE ERRORS ?
There are some typos in your script as well as problem with contraints. My preferable way to define constraints is to use separate statements instead of inline within the table creation script. I addition you can only reference primary keys (one or more column as a combound key) with a foreign key constraint. So you will either have to extend the primary key in the flights table to the DeptTime ot narrow the foreign key on the passenger table to flightNo only. As of my knowledge about flight it has to be the first solution, right ? :-)
create table passenger
(
PnrNo char(8) constraint PNR primary key clustered not null,
FlightNo char(5) not null,
FName char(20) not null,
LName char(20) not null,
Age int not null,
Gender char(1) not null,
Class char(15) not null,
SeatPref char(6) not null,
MealPref char(15) not null,
SSR varchar(100) not null,
Status char(15),
TravelDate datetime
)
ALTER TABLE Passenger
ADD CONSTRAINT FK_Passenger_Flights FOREIGN KEY (FlightNo, TravelDate)
references flights(FlightNo, DepTime)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thank you very much.In future i will use your way. I will define constraints separately. Cheers !!
|||Great reply and its works..
