SQL 错误：ORA-00922：缺少或无效的选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8419679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Error: ORA-00922: missing or invalid option
提问by Jatinder Singh
CREATE TABLE chartered flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)
Should I not use CHAR data type?
我不应该使用 CHAR 数据类型吗？
I hear it is bad practice to use it but I wanted to make it so takeoff_at and destination have to have minimum 3 characters because they are airport codes.
我听说使用它是不好的做法，但我想让它起飞，目的地必须至少有 3 个字符，因为它们是机场代码。
This is the error I am getting:
这是我得到的错误：
Error at Command Line:1 Column:23
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 - "missing or invalid option"
*Cause:
*Action:
回答by Justin Cave
The error you're getting appears to be the result of the fact that there is no underscore between "chartered" and "flight" in the table name. I assume you want something like this where the name of the table is chartered_flight.
您得到的错误似乎是因为表名中的“chartered”和“flight”之间没有下划线。我假设您想要这样的东西，其中表的名称是chartered_flight.
CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)
Generally, there is no benefit to declaring a column as CHAR(3)rather than VARCHAR2(3). Declaring a column as CHAR(3)doesn't force there to be three characters of (useful) data. It just tells Oracle to space-pad data with fewer than three characters to three characters. That is unlikely to be helpful if someone inadvertently enters an incorrect code. Potentially, you could declare the column as VARCHAR2(3)and then add a CHECKconstraint that LENGTH(takeoff_at) = 3.
通常，将列声明为CHAR(3)而不是没有任何好处VARCHAR2(3)。将列声明为CHAR(3)并不强制有（有用的）数据的三个字符。它只是告诉 Oracle 将少于三个字符的数据用空格填充到三个字符。如果有人无意中输入了错误的代码，这不太可能有帮助。潜在地，您可以将该列声明为VARCHAR2(3)，然后添加一个CHECK约束LENGTH(takeoff_at) = 3。
CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL CHECK( length( takeoff_at ) = 3 )
, destination CHAR (3) NOT NULL CHECK( length( destination ) = 3 )
)
Since both takeoff_atand destinationare airport codes, you really ought to have a separate table of valid airport codes and define foreign key constraints between the chartered_flighttable and this new airport_codetable. That ensures that only valid airport codes are added and makes it much easier in the future if an airport code changes.
由于takeoff_at和destination都是机场代码，你真的应该有一个单独的有效机场代码chartered_flight表，并在表和这个新airport_code表之间定义外键约束。这可确保仅添加有效的机场代码，并且在机场代码更改时使将来更容易。
And from a naming convention standpoint, since both takeoff_atand destinationare airport codes, I would suggest that the names be complementary and indicate that fact. Something like departure_airport_codeand arrival_airport_code, for example, would be much more meaningful.
从命名约定的角度来看，由于takeoff_at和destination都是机场代码，我建议这些名称是互补的并表明这一事实。例如，像departure_airport_code和这样的东西arrival_airport_code会更有意义。
回答by a1ex07
You should not use space character while naming database objects. Even though it's possible by using double quotes(quoted identifiers), CREATE TABLE "chartered flight" ..., it's not recommended. Take a closer look here
命名数据库对象时不应使用空格字符。尽管可以使用双引号（带引号的标识符）, CREATE TABLE "chartered flight" ...，但不建议这样做。仔细看看这里
回答by StevieG
there's nothing wrong with using CHAR like that..
I think your problem is that you have a space in your tablename. It should be: charteredflightor chartered_flight..
像那样使用 CHAR 并没有错。我认为你的问题是你的表名中有一个空格。它应该是：charteredflight或chartered_flight..
