Zach Bennett Zach Bennett - 1 month ago 7
SQL Question

Need help getting my Oracle Database working. Using SQL Developer

This is my code for my Database for some reason I cannot get it to work and am getting a couple of errors that I cannot get rid of. Anyone be able to assist me?

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)
);


I got rid of the Drop tables at the top just to show the main code. Any help would be greatly appreciated!

Here's the Errors:


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.


All the tables have the same
TABLE OR VIEW DOES NOT EXIST
error.

Answer

You need to define the referenced tables before the tables referencing them.
For example, define dealer before bike (because bike references dealer).

If you've got a circular situation where A references B and visa versa, wrap the two table create statements in a transaction with a leading BEGIN and trailing COMMIT. Note that doing this is usual and should only be done after careful consideration and if absolutely necessary.


I think there's something fundamentally wrong with your table design too; the dealer table has bikeno as part of its primary key. That seems completely wrong to me - bikeno shouldn't be in the dealer table at all, let alone be part of its key!


And this is a little insane:

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

Huh? I've never seen this before - a table referencing itself!?