question

faresnz avatar image
faresnz asked

SQL Code0092 error: whats my mistake?

hi guys here is my SQL code the Tables with marked as comment(/) does not work and when i try to execute them oracle give me error 0092 " table or item does not exist" i do not know what's wrong i tried everything but can't figure it out here is the code and i appreciate each reply cheers. CREATE TABLE JobPosition ( JobPositionID Number(2), JobPositionName VARCHAR2(45) NOT NULL, PRIMARY KEY(JobPositionID) ); CREATE TABLE PaymentMethod ( PaymentMethodID Number(2), PaymentMethodName VARCHAR2(45) NOT NULL, PRIMARY KEY(PaymentMethodID) ); CREATE TABLE NextOfKin (NOKID Number(5), NokName VARCHAR2(45) NOT NULL, Address VARCHAR2(60), ContactNumber VARCHAR2(15), PRIMARY KEY(NOKID) ); CREATE TABLE Relationship ( RelationshipID Number(2), RelationshipName VARCHAR2(45) NOT NULL, PRIMARY KEY (RelationshipID) ); CREATE TABLE StudCategory ( CategoryID Number(2), CategoryName VARCHAR2(45) NOT NULL, PRIMARY KEY (CategoryID) ); CREATE TABLE Department ( DepartmentID Number(2), DepartmentName VARCHAR2(45) NOT NULL, PRIMARY KEY (DepartmentID) ); CREATE TABLE ResidenceType ( ResidenceTypeID Number(2), ResidenceTypeName VARCHAR2(45) NOT NULL, PRIMARY KEY(ResidenceTypeID) ); CREATE TABLE Course ( CourseID Number(3), CourseTitle VARCHAR2(45) NOT NULL, Leader VARCHAR2(45) NOT NULL, IntPhoneNum VARCHAR2(15) NOT NULL, RoomNum Number(2) NOT NULL, DepartmentID Number(2) NOT NULL, PRIMARY KEY(CourseID), FOREIGN KEY(DepartmentID) REFERENCES Department(DepartmentID) ); CREATE TABLE StudAdvisor ( AdvisorID Number(5), FirstName VARCHAR2(25) NOT NULL, Middle VARCHAR2(10), FamilyName VARCHAR2(25) NOT NULL, DepartmentID Number(2) NOT NULL, InternalPhone VARCHAR2(15), PRIMARY KEY (AdvisorID), FOREIGN KEY(DepartmentID) REFERENCES Department(DepartmentID) ); CREATE TABLE AccOffice ( OfficeID VARCHAR2(6), OfficeName VARCHAR2(45) NOT NULL, Address VARCHAR2(60) NOT NULL, Phone VARCHAR2(15) NOT NULL, NumRooms Number(3) NOT NULL, PRIMARY KEY (OfficeID) ); /*CREATE TABLE ResidenceBuilding ( ResidenceID VARCHAR2(6), ResidenceName VARCHAR2(45) NOT NULL, Address VARCHAR2(60), City VARCHAR2(30) NOT NULL, Telephone VARCHAR2(15), ResidenceTypeID Number(2) NOT NULL, OfficeID VARCHAR2(6) NOT NULL, StaffID VARCHAR2(6), PRIMARY KEY (ResidenceID), FOREIGN KEY (ResidenceTypeID) REFERENCES ResidenceType (ResidenceTypeID), FOREIGN KEY (StaffID) REFERENCES Staff (StaffID) );*/ /*CREATE TABLE FlatHallRooms ( PlaceNumber Number(3), RoomNumber VARCHAR2(5) NOT NULL, Rent Number(9,2) NOT NULL, ResidenceID VARCHAR2(6) NOT NULL, NumSingleBed Number(3) NOT NULL, PRIMARY KEY(PlaceNumber), FOREIGN KEY (ResidenceID) REFERENCES ResidenceBuilding (ResidenceID) );*/ CREATE TABLE StudentNOK ( NOKID Number(5), StudentID VARCHAR2(10), RelationshipID Number(2) NOT NULL, PRIMARY KEY (NOKID,StudentID), FOREIGN KEY (NOKID) REFERENCES NextOfKin(NOKID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (RelationshipID) REFERENCES Relationship(RelationshipID) ); CREATE TABLE Student ( StudentID VARCHAR2(10), FirstName VARCHAR2(25) NOT NULL, Middle VARCHAR2(10), LastName VARCHAR2(25) NOT NULL, Address VARCHAR2(45) NOT NULL, City VARCHAR2(30) NOT NULL, DOB DATE NOT NULL, Sex VARCHAR2(1) NOT NULL, Nationality VARCHAR2(30) NOT NULL, IsSmoker VARCHAR2(1) NOT NULL, SpecialNeeds VARCHAR2(30) NOT NULL, Comments VARCHAR2(100), CategoryID Number(2) NOT NULL, AdvisorID Number(5) NOT NULL, CourseID Number(3) NOT NULL, PRIMARY KEY (StudentID), FOREIGN KEY (CategoryID) REFERENCES StudCategory (CategoryID), FOREIGN KEY (AdvisorID) REFERENCES StudAdvisor (AdvisorID), FOREIGN KEY (CourseID) REFERENCES Course (CourseID) ); /*CREATE TABLE Rent ( RentID Number(6), RentDate DATE NOT NULL, MinRentalPeriod Number(3) NOT NULL, Semester Number(1), PlaceNumber Number(3) NOT NULL, PRIMARY KEY(RentID), FOREIGN KEY (PlaceNumber) REFERENCES FlatHallRooms(PlaceNumber) );*/ /*CREATE TABLE StudentRent ( RentID Number(6), StudentID VARCHAR2(10), PRIMARY KEY (RentID,StudentID), FOREIGN KEY (RentID) REFERENCES Rent(RentID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID) );*/ /*CREATE TABLE Lease ( LeaseID Number(6), LeaseStart Date NOT NULL, Duration Number(3) NOT NULL, OfficeID VARCHAR2(6) NOT NULL, PlaceNumber Number(3) NOT NULL, LeaseAmount Number(9,2), IsPaid VARCHAR2(1) NOT NULL, PRIMARY KEY (LeaseID), FOREIGN KEY (OfficeID) REFERENCES AccOffice(OfficeID), FOREIGN KEY (PlaceNumber) REFERENCES FlatHallRooms(PlaceNumber) );*/ /*CREATE TABLE StudentLease ( LeaseID Number(6), StudentID VARCHAR2(10), ExpLeaveDate Date, PRIMARY KEY (LeaseID,StudentID), FOREIGN KEY (LeaseID) REFERENCES Lease(LeaseID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID) );*/ /*CREATE TABLE RentInvoice ( RInvoiceID NUMBER(6), InvoiceDate Date NOT NULL, RentID Number(6) NOT NULL, StudentID VARCHAR2(10) NOT NULL, AmountPaid NUMBER(9,2), Status VARCHAR2(15), OfficeID VARCHAR2(6) NOT NULL, PaymentMethodID Number(2) NOT NULL, PRIMARY KEY (RInvoiceID), FOREIGN KEY (StudentID) REFERENCES Student (StudentID), FOREIGN KEY (OfficeID) REFERENCES AccOffice(OfficeID), FOREIGN KEY (PaymentMethodID) REFERENCES PaymentMethod(PaymentMethodID) );*/ /*CREATE TABLE Staff ( StaffID VARCHAR2(6), FirstName VARCHAR2(25) NOT NULL, FamilyName VARCHAR2(25) NOT NULL, HomeAddress VARCHAR2(45) NOT NULL, City VARCHAR2(30) NOT NULL, DOB Date NOT NULL, Sex VARCHAR2(1) NOT NULL, JobPositionID Number(2) NOT NULL, ResidenceID VARCHAR2(6) NOT NULL, PRIMARY KEY (StaffID), FOREIGN KEY (JobPositionID) REFERENCES JobPosition (JobPositionID), FOREIGN KEY (ResidenceID) REFERENCES ResidenceBuilding (ResidenceID) );*/ /*CREATE TABLE Inspection ( InspectionID Number(6), InspectionDate Date NOT NULL, IsSatisfy VARCHAR2(1) NOT NULL, Comments VARCHAR2(60), StaffID VARCHAR2(6) NOT NULL, RentID Number(6) NOT NULL, StudentID VARCHAR2(10) NOT NULL, PRIMARY KEY (InspectionID), FOREIGN KEY (StaffID) REFERENCES Staff (StaffID), FOREIGN KEY (RentID, StudentID) REFERENCES StudentRent (RentID, StudentID) );*/ /*CREATE TABLE LeaseInvoice ( InvoiceID NUMBER(6), InvoiceDate Date NOT NULL, PaymentDue Number(9,2) NOT NULL, PaymentMethodID Number(2) NOT NULL, SemesterNum NUMBER(1), StudentID VARCHAR2(10) NOT NULL, LeaseID Number(6) NOT NULL, OfficeID VARCHAR2(6) NOT NULL, Status VARCHAR(2) NOT NULL, PRIMARY KEY (InvoiceID), FOREIGN KEY (StudentID) REFERENCES Student (StudentID), FOREIGN KEY (OfficeID) REFERENCES AccOffice(OfficeID), FOREIGN KEY (PaymentMethodID) REFERENCES PaymentMethod(PaymentMethodID), FOREIGN KEY (LeaseID) REFERENCES Lease(LeaseID) );*/ /*CREATE TABLE Re_mainder ( InvoiceID NUMBER(6), RemainderDate DATE, Status VARCHAR2(30) NOT NULL, StudentID VARCHAR2(10) NOT NULL, PRIMARY KEY (InvoiceID, RemainderDate), FOREIGN KEY (InvoiceID) REFERENCES LeaseInvoice (InvoiceID), FOREIGN KEY (StudentID) REFERENCES Student (StudentID) );*/
sqloracledatabasehelp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
It's erroring because your CREATE TABLE statements are trying to create foreign keys that reference tables that haven't yet been created.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.