sql - 需要帮助让我的 Oracle 数据库正常工作。使用 SQL 开发人员

Question

这是我的数据库代码，由于某种原因我无法让它工作，并且遇到了一些我无法摆脱的错误。任何人都可以帮助我吗？

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 表。任何帮助将不胜感激!

这是错误:

Error at Command Line:58 Column:6 Error report: SQL Error: ORA-02253: constraint specification not allowed here

Error at Command Line:209 Column:68 Error report: SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist"

Error at Command Line:199 Column:71 Error report: SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist"

Error at Command Line:186 Column:69 Error report: SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist"

Error at Command Line:169 Column:78 Error report: SQL Error: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column.

所有表都有相同的 TABLE OR VIEW DOES NOT EXIST 错误。

Answer 1

您需要在引用它们的表之前定义引用表。
例如，定义 dealer before bike(因为自行车引用经销商)。

如果您遇到 A 引用 B 且反之亦然的循环情况，请将两个表创建语句包装在一个事务中，前导 BEGIN 和尾随 COMMIT。请注意，这样做是很常见的，只有在经过仔细考虑并且绝对必要时才应该这样做。

我认为您的表格设计也存在根本性的错误； dealer 表将 bikeno 作为其主键的一部分。这对我来说似乎是完全错误的 - bikeno 根本不应该出现在经销商表中，更不用说成为其关键的一部分了!

这有点疯狂:

CREATE TABLE customer (
  CustNo         NUMBER(8),
  ...
  PRIMARY KEY(CustNo),  
  FOREIGN KEY(CustNo)
  REFERENCES customer (CustNo)
);

嗯？我以前从未见过这个 - 一个引用自身的表!？