时间戳

首页 > 解决方案 > 我对此脚本的引用有问题

sql - 我对此脚本的引用有问题

问题描述

将餐桌预订参考给人员和司机时出现错误。第 17 行的错误：ORA-02270：此列列表没有匹配的唯一键或主键

我已经尝试了几乎所有方法，请帮助我。

alter session set NLS_DATE_FORMAT='DD/MM/YYYY';

DROP TABLE CARSERVICE;
DROP TABLE DRIVERBEN;
DROP TABLE BENEFITS;
DROP TABLE FOLLOWUP;
DROP TABLE INCIDENT;
DROP TABLE DSESSION;
DROP TABLE TRAINING;
DROP TABLE DRIVERINS;
DROP TABLE BOOKING;
DROP TABLE DRIVER;
DROP TABLE PERSON;

CREATE TABLE PERSON
(
PID               CHAR(6) PRIMARY KEY,
PName             VARCHAR(20),
PDOB              DATE,
Sex               CHAR(1),
PMOBILE       CHAR(12)
);


CREATE TABLE DRIVER
(
DID               CHAR(6),
DGrade            CHAR(1),
DCarId            CHAR(6),
DLicense          CHAR(8),
DStart            DATE,
DIPlan            CHAR(1),
DSession         CHAR(6),
DNRIC             CHAR(12),
PID            CHAR(6),
PRIMARY KEY (DID, DCarId),
FOREIGN KEY (DID) references PERSON(PID)
);

CREATE TABLE BOOKING
(
BookingID        CHAR(6),
PickLoc        VARCHAR(250),
DropLoc           VARCHAR(250),
TripRating        NUMBER(1),
RideFare          NUMBER(*,1),
TollOther         NUMBER(*,1),
TDate             DATE,
TTime             VARCHAR(5),
BStatus           CHAR(1),
Payment           VARCHAR(12),
IncRep            CHAR(1),
PID               CHAR(6),
DID               CHAR(6),
PRIMARY KEY(BookingID,PID,DID),
FOREIGN KEY (DID) references Driver(DID),
FOREIGN KEY (PID) references PERSON(PID)
);


CREATE TABLE DRIVERINS
(
DIID               CHAR(6) PRIMARY KEY,
PAmount            NUMBER(*,1),
PDt                DATE,
FOREIGN KEY (DIID) references DRIVER (DID)
);

CREATE TABLE TRAINING
(
TID     CHAR(6) PRIMARY KEY,
TrainingPrg        VARCHAR(50),
PrgSession         VARCHAR2(10)
);

CREATE TABLE DSESSION
(
SID               CHAR(6) PRIMARY KEY,
SDate        DATE,
TID        CHAR(6),
FOREIGN KEY (SID) references DRIVER(DID),
FOREIGN KEY (TID) references TRAINING(TID)
);





CREATE TABLE INCIDENT
(
INCID              CHAR(6) PRIMARY KEY,
RIncident          VARCHAR(30),
IncDate            DATE,
FOREIGN KEY (INCID) references BOOKING(BookingID)
);

CREATE TABLE FOLLOWUP
(
FollowID              CHAR(6) PRIMARY KEY,
IncPIC                VARCHAR(30),
FollowUpDt            VARCHAR2(50),
IncStatus             CHAR(1),
FollowUpDate    DATE,
FOREIGN KEY (FollowID) references INCIDENT(INCID)
);

CREATE TABLE BENEFITS
(
BID                CHAR(6) PRIMARY KEY,
CMedBenefit        NUMBER(*,1),
OBetterCars        NUMBER(*,1),
PrepRet            NUMBER(*,1)
);

CREATE TABLE DRIVERBEN
(
DBID               CHAR(6),
CMedBenefit        CHAR(1),
OBetterCars        CHAR(1),
PrepRet            CHAR(1),
FOREIGN KEY (DBID) references DRIVER(DID)
);


CREATE TABLE CARSERVICE
(
CarID                 CHAR(6) PRIMARY KEY,
CarType               VARCHAR(20),
ServRem               VARCHAR(250),
CarServDate           DATE,
NextServD             DATE,
FOREIGN KEY (CarID) references DRIVER(DCarId)
);

标签： sqldatabaseoracle

解决方案

你有这些定义：

CREATE TABLE DRIVER (
    . . .
    PRIMARY KEY (DID, DCarId),
    . . .
);

CREATE TABLE BOOKING (
    . . 
    FOREIGN KEY (DID) references Driver(DID),
    . . .
);

这些是不一致的。的主键Driver有两个组件键。外键引用只使用一个。您需要参考两者。

推荐阅读