“表或视图不存在”

I am trying to create some tables but i get every time i try table or view does not exist.

我正在尝试创建一些表,但每当我尝试表或视图不存在时我得到。

I cant find any solution. Here is my code.

我找不到任何解决方案。这是我的代码。

 CREATE
  TABLE "User"
  (
    anon_id      NUMBER (38) NOT NULL PRIMARY KEY,
    querytime    TIMESTAMP ,
    state_symbol VARCHAR2 (5) NOT NULL
  ) ;

CREATE
  TABLE "state"
  (
    symbol            VARCHAR2 (5) NOT NULL PRIMARY KEY,
    name              VARCHAR2 (30 BYTE) ,
    "size"            NUMBER (38) ,
    population        NUMBER (38) ,
    tourists          NUMBER (38) 
  ) ;

CREATE
  TABLE "population_goup"
  (
    name              VARCHAR2 (255) NOT NULL PRIMARY KEY,
    COUNT             NUMBER (38) 
  ) ;

The first 3 tables are createt and now he starts "table or view does not exist" and i dont know why.

前3个表是createt,现在他开始“表或视图不存在”,我不知道为什么。

CREATE TABLE "location"
  (
    zip         NUMBER (38) NOT NULL PRIMARY KEY,
    city        VARCHAR2 (30 BYTE) NOT NULL,
    state_symbol VARCHAR2 (30 BYTE) NOT NULL ,
    timezone    VARCHAR2 (30 BYTE) NOT NULL ,
    latitude    FLOAT ,
    longitude   FLOAT ,
    population  NUMBER (38) NOT NULL ,
    FOREIGN KEY (state_symbol) references state(symbol)
  ) ;

CREATE
  TABLE landmark
  (
    name              VARCHAR2 (30 BYTE) NOT NULL PRIMARY KEY,
    tourists          NUMBER (38) NOT NULL ,
    location_zip      NUMBER (38) NOT NULL REFERENCES location (zip)
  ) ;


CREATE
  TABLE event
  (
    name              VARCHAR2 (255) NOT NULL ,
    from_date         TIMESTAMP ,
    to_date           TIMESTAMP ,
    location_zip      NUMBER (38) NOT NULL REFERENCES location (zip)
  ) ;


CREATE
  TABLE lives_in
  (
    population_goup_name VARCHAR2 (255) NOT NULL REFERENCES population_group(NAME),
    state_symbol         VARCHAR2 (5) NOT NULL REFERENCES state(SYMBOL),
    PRIMARY KEY(population_goup_name, state_symbol)
  ) ;

  CREATE
  TABLE "searchquery"
  (
    query        VARCHAR2 (4000 CHAR) NOT NULL PRIMARY KEY,
    User_id NUMBER (38) NOT NULL REFERENCES User(anon_id),
    state_symbol VARCHAR2 (5) REFERENCES state(symbol),
    population_name VARCHAR2 (255) REFERENCES population_goup(name),
    landmark_name  VARCHAR2 (30 BYTE) REFERENCES landmark(name),
    event_name  VARCHAR2 (255) REFERENCES event(name),
    location_zip NUMBER (38) REFERENCES landmark(zip)
  ) ;

For searchquery i get "invalid table name" but its not a reserved word so why? I cant find any help from the script of my uni so i thanks to every one that can help me!

对于searchquery,我得到“无效的表名”,但它不是保留字,为什么?我无法从我的大学脚本中找到任何帮助,所以我感谢每一个可以帮助我的人!

2 个解决方案

#1

In order, every object is saved with UPPER CASE name. Unless enclosed within double quotes.

按顺序,每个对象都以UPPER CASE名称保存。除非用双引号括起来。

Remove the double quotes to all your table names in the DDL. Also, User, State all are reserved keywords. You cannot use them as table names.

删除DDL中所有表名的双引号。此外,User,State都是保留关键字。您不能将它们用作表名。

"state" --> state

“国家” - >国家

Refer your tables in the constraints with double quotes, same as in DDL previously..

使用双引号引用约束中的表,与之前的DDL相同。

references state(symbol) --> references "state"(symbol)

引用状态(符号) - >引用“状态”(符号)

searchquery DDL ended with invalid table name because, you refer User table, which is a datadictionary table. So, embed them in double quotes

searchquery DDL以无效的表名结尾,因为您引用了User表,这是一个datadictionary表。所以,将它们嵌入双引号中

Example:

CREATE
  TABLE "searchquery"
  (
    query        VARCHAR2 (4000 CHAR) NOT NULL PRIMARY KEY,
    User_id NUMBER (38) NOT NULL REFERENCES "User"(anon_id),
    state_symbol VARCHAR2 (5) REFERENCES "state"(symbol),
    population_name VARCHAR2 (255) REFERENCES "population_goup"(name),
    landmark_name  VARCHAR2 (30 BYTE) REFERENCES "landmark"(name),
    event_name  VARCHAR2 (255) REFERENCES "event"(name),
    location_zip NUMBER (38) REFERENCES "landmark"(zip)
  ) ;

From DOCs.

Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive.

不带引号的标识符不区分大小写。 Oracle将它们解释为大写。带引号的标识符区分大小写。

By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace:

通过将名称括在双引号中,可以将以下名称赋予同一名称空间中的不同对象:

employees
"employees"
"Employees"

员工“员工”“员工”

Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace:

请注意,Oracle将以下名称解释为相同,因此它们不能用于同一名称空间中的不同对象:

employees
EMPLOYEES
"EMPLOYEES"

员工员工“员工”

More on the Schema Object Names and Qualifiers

有关模式对象名称和限定符的更多信息

#2

state is a keyword, so you double quoted it to use it as a table name. Now you need the quoted name whenever you access that table:

state是一个关键字,因此您双引号将其用作表名。现在,只要您访问该表,就需要引用的名称:

CREATE TABLE "location"
  (
    zip         NUMBER (38) NOT NULL PRIMARY KEY,
    city        VARCHAR2 (30 BYTE) NOT NULL,
    state_symbol VARCHAR2 (30 BYTE) NOT NULL ,
    timezone    VARCHAR2 (30 BYTE) NOT NULL ,
    latitude    FLOAT ,
    longitude   FLOAT ,
    population  NUMBER (38) NOT NULL ,
    FOREIGN KEY (state_symbol) references "state"(symbol)
  ) ;

#1