Oracle Apex SQL Error Ora-00942: table or view does not exist

Your FAMILYRECORD table definition refers to a table called Individual that does not exist.

By the way, Oracle uses VARCHAR2 (variable-length character) for strings. CHAR is a fixed-length string type provided for portability and not usually a good choice for general character values.

The "table or view does not exist" is pretty clear. A command is executed that references a table which does not exist.

For the "DROP TABLE" statements it is obvious: the table you're trying to drop does not exist.

For the other statements it is because you're trying to create a constraint on a table that does not exist yet. For the FAMILYRECORD table there is a constraint that references INDIVIDUAL but that table is only created later in the script. As a result ORA-942 is raised.

--Student A (Intersection Entity)--
CREATE TABLE FAMILYRECORD (
    famMobileNo CHAR(12),
    mysjID CHAR(20),
    constraint familyrecord_PK PRIMARY KEY (famMobileNo, mysjID),
    constraint familyrecord_familymember_FK FOREIGN KEY (famMobileNo) REFERENCES Familymember (famMobileNo),
    constraint familyrecord_familymember_FK FOREIGN KEY (mysjID) REFERENCES Individual(mysjID)
);

The easiest solution for this is to remove the foreign key constraint definitions from your CREATE TABLE statement and create all the constraints at the end of your script using the ALTER TABLE ADD CONSTRAINT statement.

It will help you a lot if you run your script manually, one statement at a time in the sql workshop. That will allow you to diagnose the errors for the statement that has been run. If you diagnose after running the script that will be overwhelming.

Since you're new to Oracle and Apex here are a couple of tips going forward:

• Do not use datatype CHAR. Instead use VARCHAR2
• Define identifiers in upper case (table names, column names, etc). Will save you a lot of headaches later.
• Use a separate primary key column, of type number GENERATED ALWAYS AS IDENTITY. That is a lot easier to work with than composed primary keys. If you want to enforce uniqueness over columns then use a unique index to enforce that.

It is about the order in which you have to create tables. As there are foreign key constraints involved:

• first create master
• then create child

Otherwise, Oracle complains that you're referencing a table that isn't created yet. It means that almost the whole CREATE TABLE list have to be rearranged:

SQL> --Student A--
SQL> CREATE TABLE FAMILYMEMBER (
  2      famMobileNo CHAR(12) PRIMARY KEY,
  3      familyName CHAR(100) NOT NULL
  4  );

Table created.

SQL>
SQL> --Student A--
SQL> CREATE TABLE HEALTHOFFICE (
  2      healthOfficeID CHAR(7) PRIMARY KEY,
  3      healthOfficeAddress CHAR(100) NOT NULL,
  4      telephoneNo CHAR(12) NOT NULL
  5  );

Table created.

SQL>
SQL> --Student A--
SQL> CREATE TABLE OFFICER (
  2      officerID CHAR(7) PRIMARY KEY,
  3      officerName CHAR(100) NOT NULL,
  4      healthOfficeID CHAR(7) NOT NULL,
  5      constraint officer_healthoffice_FK FOREIGN KEY (healthOfficeID) REFERENCES Healthoffice (healthOfficeID)
  6  );

Table created.

SQL>
SQL> --Student B--
SQL> CREATE TABLE CATEGORY(
  2      categoryNo INTEGER PRIMARY KEY,
  3      dateOfArrivalInMalaysia Date,
  4      FlightNoOrPublicTransportNo CHAR(8),
  5      dateOfSymptomsOnset Date,
  6      relationshipWithCase VARCHAR(20),
  7      dateOfExposure Date,
  8      CTY_type CHAR(3) NOT NULL CHECK(CTY_type IN ('PUI','CCI'))
  9  );

Table created.

SQL>
SQL> --Student B--
SQL> CREATE TABLE INDIVIDUAL(
  2      mysjID CHAR(20) PRIMARY KEY,
  3      passportIC CHAR(20) NOT NULL,
  4      name VARCHAR(35) NOT NULL,
  5      ethnicity VARCHAR(20),
  6      birthDate Date NOT NULL,
  7      mobileNo CHAR(12),
  8      housingAddress CHAR(100),
  9      dateTimeArrival Date,
 10      wristbandID CHAR(7),
 11      yearsOfLiving INTEGER,
 12      IDV_type CHAR(20) NOT NULL CHECK(IDV_type IN ('TRAVELLER','NONTRAVELLER')),
 13      officerID CHAR(7),
 14      categoryNo INTEGER NOT NULL,
 15      constraint individual_officer_FK FOREIGN KEY(officerID) REFERENCES Officer(officerID),
 16      constraint individual_category_FK FOREIGN KEY(categoryNo) REFERENCES Category(categoryNo)
 17  );

Table created.

SQL>
SQL> --Student A (Intersection Entity)-- --> rename FK
SQL> CREATE TABLE FAMILYRECORD (
  2      famMobileNo CHAR(12),
  3      mysjID CHAR(20),
  4      constraint familyrecord_PK PRIMARY KEY (famMobileNo, mysjID),
  5      constraint familyrecord_familymember_FK FOREIGN KEY (famMobileNo) REFERENCES Familymember (famMobileNo),
  6      constraint familyrecord_individual_FK FOREIGN KEY (mysjID) REFERENCES Individual(mysjID)
  7  );

Table created.

SQL>
SQL> --Student A--
SQL> CREATE TABLE COUNTRY(
  2      countryID CHAR(6) PRIMARY KEY,
  3      countryName CHAR(50) NOT NULL
  4  );

Table created.

SQL>
SQL> --Student A (Intersection Entity)--
SQL> CREATE TABLE TRAVELRECORD(
  2      countryID CHAR(6),
  3      mysjID CHAR(20),
  4      duration INTEGER CHECK(duration>0),
  5      constraint travelrecord_PK PRIMARY KEY (countryID,mysjID),
  6      constraint travelrecord_country_FK FOREIGN KEY(countryID) REFERENCES Country(countryID),
  7      constraint travelrecord_individual_FK FOREIGN KEY(mysjID) REFERENCES Individual(mysjID)
  8  );

Table created.

SQL>
SQL> --Student B--
SQL> CREATE TABLE DAILYMONITORING(
  2      dailyMonitoringID CHAR(5) PRIMARY KEY,
  3      dateOfMonitoring Date NOT NULL,
  4      patientSymptoms VARCHAR(25) NOT NULL,
  5      dayOfMonitoring INTEGER NOT NULL,
  6      mysjID CHAR(20) NOT NULL,
  7      constraint dailymonitoring_individual_FK FOREIGN KEY(mysjID) REFERENCES Individual(mysjID)
  8  );

Table created.

SQL>
SQL> --Student B--
SQL> CREATE TABLE LOCATION(
  2      QRcode CHAR(20) PRIMARY KEY,
  3      locationAddress VARCHAR(100) NOT NULL,
  4      description VARCHAR(30)
  5  );

Table created.

SQL>
SQL> --Student B--
SQL> CREATE TABLE VISITRECORD(
  2      QRcode CHAR(20),
  3      mysjID CHAR(20),
  4      dateTimeScanQR Date NOT NULL,
  5      constraint visitrecord_PK PRIMARY KEY(QRcode,mysjID),
  6      constraint visitrecord_location_FK FOREIGN KEY(QRcode) REFERENCES Location(QRcode),
  7      constraint visitrecord_individual_FK FOREIGN KEY(mysjID) REFERENCES Individual(mysjID)
  8  );

Table created.

SQL>
SQL> --Student B--
SQL> CREATE TABLE DEPENDANT(
  2      passportICdependant CHAR(20) PRIMARY KEY,
  3      dependantName CHAR(100) NOT NULL,
  4      dependantType CHAR(10) NOT NULL  CHECK (dependantType IN ('Child','OKU')),
  5      mysjID CHAR(20),
  6      constraint dependant_individual_FK FOREIGN KEY(mysjID) REFERENCES Individual(mysjID)
  7  );

Table created.

SQL>

Alternatively, you could create tables in any order you want, but without foreign keys. Then create foreign keys using the ALTER TABLE statement. It is up to you.

Also, one of the foreign keys has to be renamed; you can't have two of them with the same name (familyrecord_familymember_FK).

CHAR(100) is almost certainly wrong; should probably be VARCHAR2 datatype for most of the string columns in those tables.