table or view does not exist error for unknown reason

Question

DROP TABLE BASIC_USER;
DROP TABLE ADMIN_USER;
DROP TABLE EQUIPMENT;
DROP TABLE CAVE;
DROP TABLE DUNGEON;
DROP TABLE ADVENTURE;
DROP TABLE AUTHOR;
DROP TABLE CHARACTER_GROUP;
DROP TABLE MY_CHARACTER;
DROP TABLE MY_USER;

CREATE TABLE MY_USER (
  user_ID int NOT NULL,
  nick varchar(255) NOT NULL,
  date_of_registration date DEFAULT current_date,
  first_name varchar(255) NOT NULL,
  last_name varchar(255),
  email varchar(255) UNIQUE, 
  age int NOT NULL,
  user_location varchar(255),
  finished_adventures int DEFAULT 0 NOT NULL,
  PRIMARY KEY(user_ID)
);

CREATE TABLE MY_CHARACTER (
  character_ID int NOT NULL,
  owner_ID int REFERENCES MY_USER(user_ID),
  char_name varchar(255) NOT NULL,
  race varchar(255) NOT NULL,
  char_class varchar(255) NOT NULL,
  char_rank int NOT NULL,
  hp int NOT NULL,
  status int NOT NULL,
  CHECK (char_rank <= 99 AND char_rank >= 0),
  CHECK (hp <= 100 AND hp >= 0),
  CHECK (status <= 1 AND status >= 0),
  PRIMARY KEY(character_ID)
);

CREATE TABLE EQUIPMENT (
  equipment_ID int NOT NULL,
  owner_ID int REFERENCES MY_CHARACTER(character_ID),
  equipment_name varchar(255) NOT NULL,
  equipment_type varchar(255) NOT NULL,
  attack int NOT NULL,
  defense int NOT NULL,
  PRIMARY KEY(equipment_ID)
);

CREATE TABLE DUNGEON (
  dungeon_ID int NOT NULL,
  dungeon_location varchar(255),
  dungeon_difficulty int NOT NULL,
  goal varchar(2500),
  status int NOT NULL,
  dungeon_creator_ID int REFERENCES AUTHOR(author_ID),
  CHECK (dungeon_difficulty <= 5 AND dungeon_difficulty >= 0),
  PRIMARY KEY(dungeon_ID)
);

CREATE TABLE ADVENTURE (
  adventure_ID int NOT NULL,
  adventure_name varchar(255) NOT NULL,
  adventure_location varchar(255) NOT NULL,
  adventure_difficulty int NOT NULL,
  participant_ID int REFERENCES CHARACTER_GROUP(chargroup_ID),
  adventure_goal varchar(2500),
  creator_ID int REFERENCES AUTHOR(author_ID),
  PRIMARY KEY (adventure_ID)
);

CREATE TABLE CHARACTER_GROUP (
  chargroup_ID int NOT NULL,
  group_participant_ID int NOT NULL REFERENCES MY_CHARACTER(character_ID)
);

CREATE TABLE CAVE (
  game_room_ID int NOT NULL,
  cave_name varchar(255) NOT NULL,
  dm_ID int REFERENCES MY_USER(user_ID),
  dungeon_sub int REFERENCES DUNGEON(dungeon_ID),
  adventure_sub int REFERENCES ADVENTURE(adventure_ID),
  cave_creation_date date DEFAULT current_date NOT NULL,
  playing_group int REFERENCES CHARACTER_GROUP(chargroup_ID),
  PRIMARY KEY(game_room_ID)
);

CREATE TABLE AUTHOR (
  author_ID int NOT NULL,
  email varchar (255) UNIQUE,
  nick varchar (255) UNIQUE
);

CREATE TABLE BASIC_USER(
  basic_ID int PRIMARY KEY REFERENCES MY_USER(user_ID)
);

CREATE TABLE ADMIN_USER(
  admin_ID int PRIMARY KEY REFERENCES MY_USER(user_ID),
  permissions int NOT NULL,
  specialization varchar(255)
);

Errors:

Table BASIC_USER dropped.

Table ADMIN_USER dropped.

Table EQUIPMENT dropped.

Error starting at line : 4 in command - DROP TABLE CAVE Error report - SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action:

Error starting at line : 5 in command - DROP TABLE DUNGEON Error report - SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action:

Error starting at line : 6 in command - DROP TABLE ADVENTURE Error report - SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action: Table AUTHOR dropped.

Table CHARACTER_GROUP dropped.

Table MY_CHARACTER dropped.

Table MY_USER dropped.

Table MY_USER created.

Table MY_CHARACTER created.

Table EQUIPMENT created.

Error starting at line : 50 in command - CREATE TABLE DUNGEON (
dungeon_ID int NOT NULL, dungeon_location varchar(255),
dungeon_difficulty int NOT NULL, goal varchar(2500), status int NOT NULL, dungeon_creator_ID int REFERENCES AUTHOR(author_ID),
CHECK (dungeon_difficulty <= 5 AND dungeon_difficulty >= 0), PRIMARY KEY(dungeon_ID) ) Error report - SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action:

Error starting at line : 61 in command - CREATE TABLE ADVENTURE (
adventure_ID int NOT NULL, adventure_name varchar(255) NOT NULL,
adventure_location varchar(255) NOT NULL, adventure_difficulty int NOT NULL, participant_ID int REFERENCES CHARACTER_GROUP(chargroup_ID), adventure_goal varchar(2500),
creator_ID int REFERENCES AUTHOR(author_ID), PRIMARY KEY (adventure_ID) ) Error report - SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action: Table CHARACTER_GROUP created.

Error starting at line : 77 in command - CREATE TABLE CAVE (
game_room_ID int NOT NULL, cave_name varchar(255) NOT NULL, dm_ID int REFERENCES MY_USER(user_ID), dungeon_sub int REFERENCES DUNGEON(dungeon_ID), adventure_sub int REFERENCES ADVENTURE(adventure_ID), cave_creation_date date DEFAULT current_date NOT NULL, playing_group int REFERENCES CHARACTER_GROUP(chargroup_ID), PRIMARY KEY(game_room_ID) ) Error report - SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action: Table AUTHOR created.

Table BASIC_USER created.

Table ADMIN_USER created.

Any idea what's the cause of this "table or view does not exist" error?

Please try to provide a minimal example, removing everything else from your code that is not necessary to reproduce the problem. — Leviathan

Gurwinder Singh Gurwinder Singh · Accepted Answer · 2017-03-26T20:15:09

You can't define a foreign key without defining the referenced table first. So, all you need to do is reorder the DDLs in such a way that all the table defined before being referenced. SQL doesn't directly allow forward referencing.

There can be a scenario where you might have to define a circular dependency. So, it's better to define the all tables first and then define apply constraints.

Another alternative, as suggested by Jon, is to use CREATE SCHEMA statement which lets create multiple tables and views and perform multiple grants in your own schema in a single transaction and use forward referencing.

DROP TABLE BASIC_USER;
DROP TABLE ADMIN_USER;
DROP TABLE EQUIPMENT;
DROP TABLE CAVE;
DROP TABLE DUNGEON;
DROP TABLE ADVENTURE;
DROP TABLE AUTHOR;
DROP TABLE CHARACTER_GROUP;
DROP TABLE MY_CHARACTER;
DROP TABLE MY_USER;



CREATE TABLE AUTHOR (
  author_ID int NOT NULL,
  email varchar (255) UNIQUE,
  nick varchar (255) UNIQUE
);

CREATE TABLE MY_USER (
  user_ID int NOT NULL,
  nick varchar(255) NOT NULL,
  date_of_registration date DEFAULT current_date,
  first_name varchar(255) NOT NULL,
  last_name varchar(255),
  email varchar(255) UNIQUE, 
  age int NOT NULL,
  user_location varchar(255),
  finished_adventures int DEFAULT 0 NOT NULL,
  PRIMARY KEY(user_ID)
);



CREATE TABLE BASIC_USER(
  basic_ID int PRIMARY KEY REFERENCES MY_USER(user_ID)
);

CREATE TABLE ADMIN_USER(
  admin_ID int PRIMARY KEY REFERENCES MY_USER(user_ID),
  permissions int NOT NULL,
  specialization varchar(255)
);

CREATE TABLE MY_CHARACTER (
  character_ID int NOT NULL,
  owner_ID int REFERENCES MY_USER(user_ID),
  char_name varchar(255) NOT NULL,
  race varchar(255) NOT NULL,
  char_class varchar(255) NOT NULL,
  char_rank int NOT NULL,
  hp int NOT NULL,
  status int NOT NULL,
  CHECK (char_rank <= 99 AND char_rank >= 0),
  CHECK (hp <= 100 AND hp >= 0),
  CHECK (status <= 1 AND status >= 0),
  PRIMARY KEY(character_ID)
);

CREATE TABLE EQUIPMENT (
  equipment_ID int NOT NULL,
  owner_ID int REFERENCES MY_CHARACTER(character_ID),
  equipment_name varchar(255) NOT NULL,
  equipment_type varchar(255) NOT NULL,
  attack int NOT NULL,
  defense int NOT NULL,
  PRIMARY KEY(equipment_ID)
);

CREATE TABLE DUNGEON (
  dungeon_ID int NOT NULL,
  dungeon_location varchar(255),
  dungeon_difficulty int NOT NULL,
  goal varchar(2500),
  status int NOT NULL,
  dungeon_creator_ID int REFERENCES AUTHOR(author_ID),
  CHECK (dungeon_difficulty <= 5 AND dungeon_difficulty >= 0),
  PRIMARY KEY(dungeon_ID)
);

CREATE TABLE CHARACTER_GROUP (
  chargroup_ID int NOT NULL,
  group_participant_ID int NOT NULL REFERENCES MY_CHARACTER(character_ID)
);

CREATE TABLE ADVENTURE (
  adventure_ID int NOT NULL,
  adventure_name varchar(255) NOT NULL,
  adventure_location varchar(255) NOT NULL,
  adventure_difficulty int NOT NULL,
  participant_ID int REFERENCES CHARACTER_GROUP(chargroup_ID),
  adventure_goal varchar(2500),
  creator_ID int REFERENCES AUTHOR(author_ID),
  PRIMARY KEY (adventure_ID)
);

CREATE TABLE CAVE (
  game_room_ID int NOT NULL,
  cave_name varchar(255) NOT NULL,
  dm_ID int REFERENCES MY_USER(user_ID),
  dungeon_sub int REFERENCES DUNGEON(dungeon_ID),
  adventure_sub int REFERENCES ADVENTURE(adventure_ID),
  cave_creation_date date DEFAULT current_date NOT NULL,
  playing_group int REFERENCES CHARACTER_GROUP(chargroup_ID),
  PRIMARY KEY(game_room_ID)
);

table or view does not exist error for unknown reason

1 Answers