ORA-00942: table or view does not exist

Databases
1

Hi Guys!

I did this database assignment for my class that involved creating an ERD conceptual model and logical model (using Power*Architect) which generated a SQL Script which as usual is full of bugs.
I really don’t know what’s wrong with my code. All the relationships, FK’s, PK’s are on the powerarchitect project. I keep getting the same error for all my tables.

My guess is that the tables are not being created because of the default values? :rolleyes:

I will post the error and the script on separate posts because the forum is showing my code in a weird format (I don’t know why)

Kind Regards

2

Error


Error starting at line 127 in command:
ALTER TABLE Flight_Record ADD CONSTRAINT ACTUAL_DATE_FLIGHT_RECORD_FK
FOREIGN KEY (ad_date)
REFERENCES Actual_Date (ad_date)
NOT DEFERRABLE
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
3

there appears to be a forum software bug – if you wrote anything ahead of your CODE block, it gets overwritten by a second copy of the code block and we can’t see it

could you please repeat the question about your code?

4 

drop table Feature cascade constraints purge;
drop table Actual_Date cascade constraints purge;
drop table Actual_Time cascade constraints purge;
drop table Arrival cascade constraints purge;
drop table Departure cascade constraints purge;
drop table Flight cascade constraints purge;
drop table Flight_Record cascade constraints purge;
drop table Country cascade constraints purge;
drop table Language cascade constraints purge;
drop table Region cascade constraints purge;
drop table Population cascade constraints purge;
drop table City cascade constraints purge;

CREATE TABLE Actual_Date (
                ad_date DATE DEFAULT DD-MM-YYYY NOT NULL,
                aa_date DATE DEFAULT DD-MM-YYYY NOT NULL,
                CONSTRAINT ACTUAL_DATE_PK PRIMARY KEY (ad_date)
);


CREATE TABLE Actual_Time (
                ad_time DATE DEFAULT HH:MM:SS NOT NULL,
                aa_time DATE DEFAULT HH:MM:SS NOT NULL,
                CONSTRAINT ACTUAL_TIME_PK PRIMARY KEY (ad_time)
);


CREATE TABLE Arrival (
                arrival_city VARCHAR2(15) NOT NULL,
                arrival_country VARCHAR2(15) NOT NULL,
                sa_date DATE DEFAULT DD-MM-YYYY NOT NULL,
                sa_time DATE DEFAULT HH:MM:SS NOT NULL,
                CONSTRAINT ARRIVAL_PK PRIMARY KEY (arrival_city)
);


CREATE TABLE Departure (
                origin_city VARCHAR2(15) NOT NULL,
                departure_country VARCHAR2(15) NOT NULL,
                sd_date DATE DEFAULT DD-MM-YYYY NOT NULL,
                sd_time DATE DEFAULT HH:MM:SS NOT NULL,
                CONSTRAINT DEPARTURE_PK PRIMARY KEY (origin_city)
);


CREATE TABLE Flight (
                flight_number VARCHAR2 DEFAULT code-number NOT NULL,
                airline_name VARCHAR2(15) NOT NULL,
                origin_city VARCHAR2(15) NOT NULL,
                arrival_city VARCHAR2(15) NOT NULL,
                CONSTRAINT FLIGHT_PK PRIMARY KEY (flight_number)
);


CREATE TABLE Flight_Record (
                flight_number VARCHAR2 DEFAULT code-number NOT NULL,
                origin_city VARCHAR2(15) NOT NULL,
                arrival_city VARCHAR2(15) NOT NULL,
                ad_time DATE DEFAULT HH:MM:SS NOT NULL,
                ad_date DATE DEFAULT DD-MM-YYYY NOT NULL,
                CONSTRAINT FLIGHT_RECORD_PK PRIMARY KEY (flight_number, origin_city, arrival_city)
);


CREATE TABLE Country (
                country_code CHAR(2) NOT NULL,
                name VARCHAR2(15) NOT NULL,
                capital_city VARCHAR2(15) NOT NULL,
                currency CHAR(3) NOT NULL,
                land_area NUMBER NOT NULL,
                CONSTRAINT COUNTRY_NAME PRIMARY KEY (country_code)
);


CREATE TABLE Language (
                language_code CHAR(2) NOT NULL,
                name VARCHAR2(15) NOT NULL,
                country_code CHAR(2) NOT NULL,
                CONSTRAINT LANGUAGE_PK PRIMARY KEY (language_code)
);


CREATE TABLE Region (
                region_code VARCHAR2 DEFAULT CC-CCC NOT NULL,
                country_code CHAR(2) NOT NULL,
                CONSTRAINT REGION_CODE PRIMARY KEY (region_code)
);


CREATE TABLE City (
                city_name VARCHAR2(15) NOT NULL,
                longitude NUMBER(8) NOT NULL,
                latitude NUMBER(8) NOT NULL,
                elevation NUMBER NOT NULL,
                timezone_GMT TIMESTAMP NOT NULL,
                pop_count NUMBER DEFAULT 0 NOT NULL,
                region_code VARCHAR2 DEFAULT CC-CCC NOT NULL,
                CONSTRAINT CITYNAME PRIMARY KEY (city_name)
);


CREATE TABLE Population (
                pop_count NUMBER DEFAULT 0 NOT NULL,
                source VARCHAR2(50) NOT NULL,
                census_date DATE NOT NULL,
                city_name VARCHAR2(15) NOT NULL,
                CONSTRAINT POPULATION_COUNT PRIMARY KEY (pop_count)
);


CREATE TABLE Feature (
                feature_name VARCHAR2(25) NOT NULL,
                city_name VARCHAR2(15) NOT NULL,
                address VARCHAR2(20) NOT NULL,
                phone_number VARCHAR2(10) DEFAULT +00-(00)-000-0000 NOT NULL,
                description VARCHAR2(200) NOT NULL,
                type CHAR(2) NOT NULL
                CONSTRAINT CHK_type_UPPER CHECK
                  (upper(type) = type)
              	CONSTRAINT CHK_type CHECK
                  (type in ('BG','HS','MU')),
                CONSTRAINT FEATURE_NAME PRIMARY KEY (feature_name)
);



ALTER TABLE Flight_Record ADD CONSTRAINT ACTUAL_DATE_FLIGHT_RECORD_FK
FOREIGN KEY (ad_date)
REFERENCES Actual_Date (ad_date)
NOT DEFERRABLE;


ALTER TABLE Flight_Record ADD CONSTRAINT ACTUAL_TIME_FLIGHT_RECORD_FK
FOREIGN KEY (ad_time)
REFERENCES Actual_Time (ad_time)
NOT DEFERRABLE;


ALTER TABLE Flight_Record ADD CONSTRAINT FLIGHT_RECORD_ARRIVAL_FK
FOREIGN KEY (arrival_city)
REFERENCES Arrival (arrival_city)
ON DELETE CASCADE
NOT DEFERRABLE;


ALTER TABLE Flight ADD CONSTRAINT FLIGHT_ARRIVAL_FK
FOREIGN KEY (arrival_city)
REFERENCES Arrival (arrival_city)
NOT DEFERRABLE;


ALTER TABLE Flight_Record ADD CONSTRAINT FLIGHT_RECORD_DEPARTURE_FK
FOREIGN KEY (origin_city)
REFERENCES Departure (origin_city)
ON DELETE CASCADE
NOT DEFERRABLE;


ALTER TABLE Flight ADD CONSTRAINT DEPARTURES_FLIGHTS_FK
FOREIGN KEY (origin_city)
REFERENCES Departure (origin_city)
ON DELETE CASCADE
NOT DEFERRABLE;

ALTER TABLE Flight_Record ADD CONSTRAINT FLIGHT_FLIGHT_RECORD_FK
FOREIGN KEY (flight_number)
REFERENCES Flight (flight_number)
NOT DEFERRABLE;


ALTER TABLE Region ADD CONSTRAINT COUNTRY_REGION_FK
FOREIGN KEY (country_code)
REFERENCES Country (country_code)
ON DELETE CASCADE
NOT DEFERRABLE;


ALTER TABLE Language ADD CONSTRAINT LANGUAGE_COUNTRY_FK
FOREIGN KEY (country_code)
REFERENCES Country (country_code)
ON DELETE CASCADE
NOT DEFERRABLE;


ALTER TABLE City ADD CONSTRAINT CITY_REGION_FK
FOREIGN KEY (region_code)
REFERENCES Region (region_code)
ON DELETE CASCADE
NOT DEFERRABLE;


ALTER TABLE Feature ADD CONSTRAINT CITY_FEATURE_FK
FOREIGN KEY (city_name)
REFERENCES City (city_name)
ON DELETE CASCADE
NOT DEFERRABLE;


ALTER TABLE Population ADD CONSTRAINT POPULATION_CITY_FK
FOREIGN KEY (city_name)
REFERENCES City (city_name)
NOT DEFERRABLE;
5

Yes, seems like a bug.
I have separated the error message and the sql script from the original post. Hope it helps.