这是我的数据库的代码，由于某种原因，我无法让它工作，并得到一些我无法摆脱的错误 . 有人能帮助我吗？
CREATE TABLE customer
(CustNo NUMBER(8),
CustFname VARCHAR2(20),
CustLname VARCHAR2(25) CONSTRAINT nn_lname NOT NULL,
CustAdress1 VARCHAR2(30),
CustAdress2 VARCHAR2(25),
CustAdress3 VARCHAR2(25),
CustPcode VARCHAR2(7) NOT NULL,
CustEmail VARCHAR2(30) CONSTRAINT cust_email UNIQUE,
CustMobile NUMBER(14),
CustPhone NUMBER(14),
OpenForOffers CHAR(1),
CONSTRAINT CustID1_PK
PRIMARY KEY(CustNo),
FOREIGN KEY(CustNo)
REFERENCES customer (CustNo)
);
CREATE TABLE bike
(BikeNo NUMBER(8) PRIMARY KEY,
DealerNo NUMBER(8) NOT NULL,
PurchasePrice NUMBER(8) NOT NULL,
PurchaseDate DATE NOT NULL,
SellPrice NUMBER(10),
SellDate NUMBER(10)
CONSTRAINT fk_DealerNo FOREIGN KEY (DealerNo)
REFERENCES Dealer (DealerNo)
);
CREATE TABLE ClassBikeSize
(BikeNo NUMBER(8),/* FK */
BikeModel VARCHAR(10),
BikeClass VARCHAR(10),
BikeSize VARCHAR(6),
PRIMARY KEY(BikeNo),
CONSTRAINT fk_bikeNo FOREIGN KEY (BikeNo)
REFERENCES bike (BikeNo));
CREATE TABLE Rental
(CustNo NUMBER(8),/* FK */
BikeNo NUMBER(8),/* FK */
TimeRented VARCHAR2(2),
TimeDueBack VARCHAR2(2),
ReservationDate DATE CONSTRAINT nn_reservationdate NOT NULL,
RentalPaid CHAR(1),
ReservationPaid VARCHAR2(3),
ReturnedLate CHAR(1),
PRIMARY KEY(BikeNo, CustNo),
CONSTRAINT fk_Rental_Bike FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo),
CONSTRAINT fk_Rental_Cust FOREIGN KEY (custNo) REFERENCES customer (CustNo)
);
CREATE TABLE Dealer
(DealerNo NUMBER(8),
BikeNo NUMBER(8),
DealerFname VARCHAR2(10),
DealerLname VARCHAR2(10) CONSTRAINT nn_DealerLname NOT NULL,
DealerAdress1 VARCHAR2(20),
DealerAdress2 VARCHAR2(20),
DealerAdress3 VARCHAR2(20),
DealerPcode VARCHAR2(8),
DealerEmail VARCHAR2(30),
DealerMob NUMBER(14),
DealerPhone NUMBER(14),
PRIMARY KEY (BikeNo, DealerNo),
FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo)
);
CREATE TABLE Employee
(EmpNo NUMBER(8)CONSTRAINT EmpNo_PK PRIMARY KEY,
EmpFname VARCHAR2(10),
EmpLname VARCHAR2(10) CONSTRAINT nn_emp_Lname NOT NULL,
EmpAddress1 VARCHAR2(20),
EmpAddress2 VARCHAR2(20),
EmpAddress3 VARCHAR2(20),
EmpPCode VARCHAR2(8) CONSTRAINT nn_emp_pcode NOT NULL,
EmpEmail VARCHAR2(30),
EmpMob NUMBER(14),
EmpPhone NUMBER(14)
);
CREATE TABLE Manufacturer
(ManuNo NUMBER(8),
BikeNo NUMBER(8), /* FK */
ManuFname VARCHAR2(10),
ManuLname VARCHAR2(10),
ManuAddress1 VARCHAR2(20),
ManuAddress2 VARCHAR2(20),
ManuAddress3 VARCHAR2(20),
ManuPcode VARCHAR2(8),
ManuEmail VARCHAR2(30),
ManuMob NUMBER(14),
ManuPhone NUMBER(14),
PRIMARY KEY (ManuNo, BikeNo),
CONSTRAINT fk_manu_bikeNo FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo)
);
CREATE TABLE Part
(PartNo NUMBER(8),
ManuNo NUMBER(8),/* FK */
BikeNo NUMBER(8),/* FK */
PartPrice VARCHAR2(3),
PartSellDate DATE CONSTRAINT nn_selldate NOT NULL,
PartSupplied VARCHAR(3),
PRIMARY KEY (PartNo, ManuNo, BikeNo),
CONSTRAINT fk_part_ManuNo FOREIGN KEY (ManuNo) REFERENCES Manufacturer (ManuNo),
CONSTRAINT fk_part_BikeNo FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo)
);
CREATE TABLE Maintenance
(MainNo NUMBER(8),
BikeNo NUMBER(8),/* FK */
Fault VARCHAR(145)CONSTRAINT nn_bikefault NOT NULL,
FaultDate DATE CONSTRAINT nn_faultdate NOT NULL,
Action VARCHAR(10),
ActionDate DATE CONSTRAINT nn_actiondate NOT NULL,
ActionSuccess VARCHAR(3),
PRIMARY KEY (BikeNo, MainNo),
CONSTRAINT fk_main_BikeNo FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo)
);
CREATE TABLE Delivery
(DelNo NUMBER(8),
EmpNo NUMBER(8)NOT NULL,/* FK */
DelTime VARCHAR2(10),
PRIMARY KEY (DelNo, EmpNo),
CONSTRAINT fk_del_emp FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo)
);
CREATE TABLE DeliOrder
(DelNo NUMBER(8),/* FK */
EmpNo NUMBER(8),/* FK */
OrderNo NUMBER(8),/* FK */
PartsDeli VARCHAR2(3),
PRIMARY KEY (DelNo, EmpNo, OrderNo),
CONSTRAINT fk_delicorder_DelNo FOREIGN KEY (DelNo) REFERENCES Delivery (DelNo),
CONSTRAINT fk_delicorder_EmpNo FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo),
CONSTRAINT fk_delicorder_OrderNo FOREIGN KEY (OrderNo) REFERENCES OrderPart (OrderNo)
);
CREATE TABLE OrderPart
(OrderNo NUMBER(8),
EmpNo NUMBER(8),/* FK */
PartNo NUMBER(8),/* FK */
ManuNo NUMBER(8),/* FK */
BikeNo NUMBER(8),/* FK */
DeliNo NUMBER(8),/* FK */
OrderDate DATE,
Orderplaced VARCHAR(3),
ArrivalDate VARCHAR(10),
PRIMARY KEY (OrderNo, EmpNo, PartNo, ManuNo, BikeNo, DeliNo),
CONSTRAINT fk_orderpart_EmpNo FOREIGN KEY (EmpNo) REFERENCES Employee (EmpNo),
CONSTRAINT fk_orderpart_PartNo FOREIGN KEY (PartNo) REFERENCES Part (PartNo),
CONSTRAINT fk_orderpart_ManuNo FOREIGN KEY (ManuNo) REFERENCES Manufacturer (ManuNo),
CONSTRAINT fk_orderpart_BikeNo FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo),
CONSTRAINT fk_orderpart_DeliNo FOREIGN KEY (DeliNo) REFERENCES Delivery (DelNo)
);
CREATE TABLE OrderLine
(OrderNo NUMBER(8), /* FK */
ManuNo NUMBER(8), /* FK */
BikeNo NUMBER(8), /* FK */
LineCost VARCHAR2(10),
QuantOrdered VARCHAR2(999),
PRIMARY KEY (OrderNo, ManuNo, BikeNo),
CONSTRAINT fk_orderline_OrderNo FOREIGN KEY (OrderNo) REFERENCES OrderPart (OrderNo),
CONSTRAINT fk_orderline_ManuNo FOREIGN KEY (ManuNo) REFERENCES Manufacturer (ManuNo),
CONSTRAINT fk_orderline_BikeNo FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo)
);
CREATE TABLE ManuPart
(ManuNo NUMBER(8),/* FK */
BikeNo NUMBER(8),/* FK */
Part VARCHAR2(10),
PRIMARY KEY (ManuNo, BikeNo),
CONSTRAINT fk_ManuPart_ManuNo FOREIGN KEY (ManuNo) REFERENCES Manufacturuer (ManuNo),
CONSTRAINT fk_manupart_bikeNo FOREIGN KEY (BikeNo) REFERENCES bike (BikeNo)
);
我摆脱了顶部的Drop表只是为了显示主要代码 . 任何帮助将不胜感激！
这是错误：
命令行错误：58列：6错误报告：SQL错误：ORA-02253：此处不允许约束规范命令行错误：209列：68错误报告：SQL错误：ORA-00942：表或视图不存在00942. 00000 - “表或视图不存在”命令行错误：199列：71错误报告：SQL错误：ORA-00942：表或视图不存在00942. 00000 - “表或视图不存在”错误在命令行：186列：69错误报告：SQL错误：ORA-00942：表或视图不存在00942. 00000 - “表或视图不存在”命令行错误：169列：78错误报告：SQL错误：ORA-02270：此列列表02270没有匹配的唯一键或主键.00000 - “此列没有匹配的唯一键或主键 .
所有表都有相同的 TABLE OR VIEW DOES NOT EXIST 错误 .
1 回答
您需要在引用它们的表之前定义引用的表 .
例如，在
bike之前定义dealer（因为自行车参考经销商） .如果你有一个循环的情况，其中A引用B，反之亦然，请将两个表create语句包含在一个带有前导
BEGIN和尾随COMMIT的事务中 . 请注意，这样做是很平常的，只有在仔细考虑后才能完成，如果绝对必要的话 .我认为你的 table 设计也存在根本性的问题;
dealer表的bikeno作为其主键的一部分 . 这对我来说似乎完全没错 -bikeno根本不应该在经销商的 table 上，更不用说成为其关键的一部分了！这有点疯狂：
咦？我以前从未见过这个 - 一张引用自己的 table ！？