PostgreSQL约束问题

问题描述

我遇到了这两个错误：

错误：没有给定引用表 flight的键匹配的唯一约束

ERROR: there is no unique constraint matching given keys for referenced table "flight"

*** 错误 ** *

*** Error ***

错误：没有给定引用表 flight的键的唯一约束匹配

ERROR: there is no unique constraint matching given keys for referenced table "flight"

这是我的代码：

CREATE TABLE Staff (
 EmployeeNumber int NOT NULL,
 FirstName char(15) NOT NULL,
 LastName char(15) NOT NULL,
 SocialSecurity int NOT NULL,
 Sex  char(1) NOT NULL,
 Address  char(20) NOT NULL,
 City  char(20) NOT NULL,
 Province char(15) NOT NULL,
 Country  char(20) NOT NULL,
 Primary Key (EmployeeNumber)
 );

CREATE TABLE FlightAttendent (
 FALN  int,
 StaffRole char (20) NOT NULL,
 EmployeeNumber int NOT NULL,
 Foreign Key (EmployeeNumber) References Staff(EmployeeNumber),
 Primary Key (FALN)
 );

Create TABLE AircraftType (
 ACType  char (10),
 Instrument char(1) NOT NULL,
 Engines  int NOT NULL,
 CrewCount int NOT NULL,
 PassengerCount int NOT NULL,
 Primary Key (ACType)
 );


CREATE TABLE Pilot (
 PILN  int,
 MedicalValid date NOT NULL,
 StaffRole char (20) NOT NULL,
 EmployeeNumber  int NOT NULL,
 AircraftType char (10) NOT NULL,
 Foreign Key (EmployeeNumber) references Staff(EmployeeNumber),
 Foreign Key (AircraftType) References AircraftType(ACType),
 Primary Key (PILN)
 );

Create TABLE Aircraft (
 AircraftID  char(6) NOT NULL,
 AircraftManufacturer char(10) NOT NULL,
 AircraftType  char(10) NOT NULL,
 Foreign Key (AircraftType) References AircraftType(ACType),
 Primary Key (AircraftID)
 );

CREATE Table Airport (
 AirportCode char(4) NOT NULL,
 AirportName char(40) NOT NULL,
 City  char(20) NOT NULL,
 Country  char(20) NOT NULL,
 Continent char(20) NOT NULL,
 Primary Key (AirportCode)
 );

Create TABLE Flight (
 FlightID  char (20),
 FlightDate  date,
 AircraftID  char(6) NOT NULL,
 ArrivalAirport  char(4) NOT NULL,
 DepartureAirport char(4) NOT NULL,
 PRIMARY KEY (FlightID, FlightDate),
 FOREIGN Key (ArrivalAirport) references Airport(AirportCode),
 FOREIGN Key (DepartureAirport) references Airport(AirportCode),
 FOREIGN KEY (AircraftID) references Aircraft(AircraftID)
 );


Create TABLE FlightCrew (
 FlightID char (20) REFERENCES Flight(FlightID) ON DELETE CASCADE,
 FlightDate date REFERENCES Flight(FlightDate) ON DELETE CASCADE,
 EmployeeNumber int NOT NULL,
 StaffRole char(20) NOT NULL,
 PRIMARY KEY(FlightID, FlightDate),
 Foreign Key (EmployeeNumber) references Staff(EmployeeNumber)
 );

CREATE Table Passenger (
 PassengerNumber int,
 PassportNumber int NOT NULL,
 Citizenship char (20) NOT NULL,
 FirstName char (20) NOT NULL,
 LastName char (20) NOT NULL,
 Primary Key (PassengerNumber)
 );

CREATE Table PassengerManifest (
 FlightID char(20),
 FlightDate date,
 PassengerNumber int NOT NULL,
 Foreign Key (FlightDate) References Flight(FlightDate),
 Foreign Key (PassengerNumber) References Passenger(PassengerNumber),
 Primary Key (FlightID, FlightDate)
 );

我做错了什么？谢谢！

推荐答案

当主键中有多个值时，您需要以不同的方式将其引用为外键。

When you have multiple values in a primary key, you need to reference it differently as a foreign key.

基本上，当你说

FlightID char (20) REFERENCES Flight(FlightID) ON DELETE CASCADE,

PostgreSQL检查该主键不存在（因为

PostgreSQL checks for that primary key, which doesn't exist (since the primary key on that table is (flightid, flightdate)).

因此，在引用航班时删除 REFERENCES 子句表，并添加

So drop the REFERENCES clauses when referencing the flight table, and add

FOREIGN KEY (FlightID, FlightDate) REFERENCES Flight (FlightID, FlightDate)

您在某些其他表定义中的使用方式。

In the manner you have in some of the other table definitions.

这篇关于PostgreSQL约束问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！