SQL ORA-00907: 缺少右括号

ORA-00907: missing right parenthesis

提问by user3806509

I have been looking at this code for the past two days now and I can not seem to get it to work. It keeps giving me

过去两天我一直在查看此代码，但似乎无法使其正常工作。它不断给我

ORA-00907: missing right parenthesis.

ORA-00907: missing right parenthesis.

I know that this is a topic that comes up a lot but for some reason none of the examples I have seen has helped me. Can someone please tell me why I got this error and how do I fix it? I am pretty sure that it has nothing to do with my parenthesis, maybe it's my CONSTRAINTS?

我知道这是一个经常出现的话题，但出于某种原因，我所看到的例子都没有帮助我。有人可以告诉我为什么会出现此错误以及如何修复它？我很确定它与我的括号无关，也许是我的约束？

DROP TABLE T_customers CASCADE CONSTRAINTS;
DROP TABLE dvd_collection CASCADE CONSTRAINTS;
DROP TABLE vhs_collection CASCADE CONSTRAINTS;

CREATE TABLE T_customers   (


                           customer_id         VARCHAR2 (8) PRIMARY KEY,
                           last_name           VARCHAR2 (30) NOT NULL,
                           first_name          VARCHAR2 (20) NOT NULL,
                           street             VARCHAR2 (30) NOT NULL,
                           city               VARCHAR2 (30) NOT NULL,
                           state                 CHAR (2) NOT NULL,
                                    CHECK (state IN ('GA','DC','VA','NY')),
                           zip_code           CHAR (5)
                                    CHECK (TO_NUMBER(zip_code)
                              BETWEEN 10000 AND 27999),
                           home_phone         VARCHAR2 (12) UNIQUE,
                           work_phone         VARCHAR2 (12) UNIQUE,
                           email                 VARCHAR2 (95) NOT NULL);




CREATE TABLE historys_T    (

          history_record       VARCHAR2 (8),
          customer_id       VARCHAR2 (8), 
          CONSTRAINT historys_T_FK FOREIGN KEY (customer_id) REFERENCES T_customer
                                       ON DELETE CASCADE,
                           order_id           VARCHAR2 (10) NOT NULL,
                                 CONSTRAINT fk_order_id_orders  
                                       REFERENCES orders
                                       ON DELETE CASCADE);


CREATE TABLE orders     (

                           order_id           VARCHAR2 (10) PRIMARY KEY,
                           m_p_unique_id       VARCHAR2 (10),
                                    CONSTRAINT orders_FK FOREIGN KEY (m_p_unique_id) REFERENCES library (m_p_unique_id)
                           order_date          DATE DEFAULT);



CREATE TABLE library_T     (

                           m_p_unique_id       VARCHAR2 (10)  PRIMARY KEY,
                           movie_title         VARCHAR2 (80)  NOT NULL,
                           serial_number       VARCHAR2 (10)  NOT NULL,
                           movie_id_number   VARCHAR2 (10)  NOT NULL,
                           movie_cast        VARCHAR2 (100) NOT NULL,
                           movie_format    CHAR (3) NOT NULL, 
                                  CONSTRAINT library_FK REFERENCES formats (movie_format));

CREATE TABLE formats_T     (

                           movie_format      CHAR (3) PRIMARY KEY,
                           movie_title       VARCHAR2 (80) NOT NULL,
                           m_p_unique_id     VARCHAR2 (10) NOT NULL,
                                 CONSTRAINT format_FK  REFERENCES library (m_p_unique_id));



CREATE TABLE dvd_collection (      


                           m_p_unique_id       VARCHAR2 (10) NOT NULL,
                           serial_number       VARCHAR2 (10) NOT NULL,
                           movie_id_number  VARCHAR2 (10) NOT NULL,
                           movie_title         VARCHAR2 (80) NOT NULL,
                           movie_cast          VARCHAR2 (100) NOT NULL,
                           movie_format     VARCHAR2 (80) NOT NULL,
                           movie_rating    VARCHAR2 (6) NOT NULL,
                           movie_distributer    VARCHAR2 (30) NOT NULL,
                           movie_price         NUMBER (3,2) NOT NULL,
                           movie_length     NUMBER (3) NOT NULL,
                           movie_award         VARCHAR2 (175) NOT NULL,
                           movie_release       DATE); 


CREATE TABLE vhs_collection            
(

                           m_p_unique_id       VARCHAR2 (10)NOT NULL,
                           serial_number       VARCHAR2 (10) NOT NULL,
                           movie_id_number  VARCHAR2 (10) NOT NULL,
                           movie_title         VARCHAR2 (80) NOT NULL,
                           movie_cast        VARCHAR2 (100) NOT NULL,
                           movie_format    VARCHAR2 (80) NOT NULL,
                           movie_rating    VARCHAR2 (6) NOT NULL,
                           movie_distributer    VARCHAR2 (30) NOT NULL,
                           movie_price         NUMBER (3,2) NOT NULL,
                           movie_length     NUMBER (3) NOT NULL,
                           movie_award         VARCHAR2 (175) NOT NULL,
                           movie_release        DATE);

Here are the results I get when I run the code:

以下是我运行代码时得到的结果：

Table dropped.

Table dropped.

Table dropped.

Table created.

                                       ON DELETE CASCADE)
                                       *

ERROR at line 10:
ORA-00907: missing right parenthesis

                           order_date          DATE DEFAULT)
                           *

ERROR at line 6:
ORA-00907: missing right parenthesis

                                  CONSTRAINT library_FK REFERENCES formats (movie_format))
                                                                           *

ERROR at line 9:
ORA-00907: missing right parenthesis

                                 CONSTRAINT format_FK  REFERENCES library (m_p_unique_id))
                                                                          *

ERROR at line 6:
ORA-00907: missing right parenthesis
Table created.

Table created.               

回答by APC

ORA-00907: missing right parenthesis

ORA-00907: 缺少右括号

This is one of several generic error messages which indicate our code contains one or more syntax errors. Sometimes it may mean we literally have omitted a right bracket; that's easy enough to verify if we're using an editor which has a match bracketcapability (most text editors aimed at coders do). But often it means the compiler has come across a keyword out of context. Or perhaps it's a misspelled word, a space instead of an underscore or a missing comma.

这是表明我们的代码包含一个或多个语法错误的几个通用错误消息之一。有时这可能意味着我们从字面上省略了一个右括号；这很容易验证我们是否使用具有匹配括号功能的编辑器（大多数针对编码人员的文本编辑器都这样做）。但通常这意味着编译器遇到了上下文之外的关键字。或者它可能是拼写错误的单词、空格而不是下划线或缺少逗号。

Unfortunately the possible reasons why our code won't compile is virtually infinite and the compiler just isn't clever enough to distinguish them. So it hurls a generic, slightly cryptic, message like ORA-00907: missing right parenthesisand leaves it to us to spot the actual bloomer.

不幸的是，我们的代码无法编译的可能原因几乎是无限的，而且编译器不够聪明，无法区分它们。所以它会抛出一个通用的、有点神秘的消息ORA-00907: missing right parenthesis，然后让我们去发现真正的大器晚成。

The posted script has several syntax errors. First I will discuss the error which triggers that ORA-0097 but you'll need to fix them all.

发布的脚本有几个语法错误。首先，我将讨论触发 ORA-0097 的错误，但您需要将它们全部修复。

Foreign key constraints can be declared in line with the referencing column or at the table level after all the columns have been declared. These have different syntaxes; your scripts mix the two and that's why you get the ORA-00907.

可以根据引用列或在声明所有列之后在表级别声明外键约束。它们有不同的语法；您的脚本将两者混合在一起，这就是您获得 ORA-00907 的原因。

In-line declaration doesn't have a comma and doesn't include the referencing column name.

行内声明没有逗号，也不包括引用列名。

CREATE TABLE historys_T    (
    history_record    VARCHAR2 (8),
    customer_id       VARCHAR2 (8) 
          CONSTRAINT historys_T_FK FOREIGN KEY REFERENCES T_customers ON DELETE CASCADE,
    order_id           VARCHAR2 (10) NOT NULL,
          CONSTRAINT fk_order_id_orders REFERENCES orders ON DELETE CASCADE)

Table level constraints are a separate component, and so do have a comma and do mention the referencing column.

表级约束是一个单独的组件，因此确实有一个逗号并提及引用列。

CREATE TABLE historys_T    (
    history_record    VARCHAR2 (8),
    customer_id       VARCHAR2 (8),    
    order_id           VARCHAR2 (10) NOT NULL,
    CONSTRAINT historys_T_FK FOREIGN KEY (customer_id) REFERENCES T_customers ON DELETE CASCADE,   
   CONSTRAINT fk_order_id_orders FOREIGN KEY (order_id) REFERENCES orders ON DELETE CASCADE)

Here is a list of other syntax errors:

以下是其他语法错误的列表：

1. The referenced table (and the referenced primary key or unique constraint) must already exist before we can create a foreign key against them. So you cannot create a foreign key for HISTORYS_Tbefore you have created the referenced ORDERStable.
2. You have misspelled the names of the referenced tables in some of the foreign key clauses (LIBRARY_Tand FORMAT_T).
3. You need to provide an expression in the DEFAULT clause. For DATE columns that is usually the current date, DATE DEFAULT sysdate.

1. 被引用的表（以及被引用的主键或唯一约束）必须已经存在，然后我们才能针对它们创建外键。因此，HISTORYS_T在创建引用ORDERS表之前，您无法为其创建外键。
2. 您在某些外键子句 (LIBRARY_T和FORMAT_T) 中拼错了引用表的名称。
3. 您需要在 DEFAULT 子句中提供表达式。对于通常是当前日期的 DATE 列，DATE DEFAULT sysdate.

Looking at our own code with a cool eye is a skill we all need to gain to be successful as developers. It really helps to be familiar with Oracle's documentation. A side-by-side comparison of your code and the examples in the SQL Reference would have helped you resolved these syntax errors in considerably less than two days. Find it here (11g)and here (12c).

以冷静的眼光看待我们自己的代码是我们成为成功开发人员都需要获得的一项技能。熟悉 Oracle 的文档确实很有帮助。将您的代码与 SQL 参考中的示例进行并排比较，可以帮助您在两天之内解决这些语法错误。在此处 (11g)和此处 (12c)找到它。

As well as syntax errors, your scripts contain design mistakes. These are not failures, but bad practice which should not become habits.

除了语法错误之外，您的脚本还包含设计错误。这些不是失败，而是不应该成为习惯的坏习惯。

1. You have not named most of your constraints. Oracle will give them a default name but it will be a horrible one, and makes the data dictionary harder to understand. Explicitly naming every constraint helps us navigate the physical database. It also leads to more comprehensible error messages when our SQL trips a constraint violation.
2. Name your constraints consistently. HISTORY_Thas constraints called historys_T_FKand fk_order_id_orders, neither of which is helpful. A useful convention is <child_table>_<parent_table>_fk. So history_customer_fkand history_order_fkrespectively.
3. It can be useful to create the constraints with separate statements. Creating tables then primary keys then foreign keys will avoid the problems with dependency ordering identified above.
4. You are trying to create cyclic foreign keysbetween LIBRARY_Tand FORMATS. You could do this by creating the constraints in separate statement but don't: you will have problems when inserting rows and even worse problems with deletions. You should reconsider your data model and find a way to model the relationship between the two tables so that one is the parent and the other the child. Or perhaps you need a different kind of relationship, such as an intersection table.
5. Avoid blank lines in your scripts. Some tools will handle them but some will not. We can configure SQL*Plus to handle thembut it's better to avoid the need.
6. The naming convention of LIBRARY_Tis ugly. Try to find a more expressive name which doesn't require a needless suffix to avoid a keyword clash.
7. T_CUSTOMERSis even uglier, being both inconsistent with your other tables and completely unnecessary, as customersis not a keyword.

1. 您尚未命名大部分约束。Oracle 会给它们一个默认名称，但这将是一个可怕的名称，并使数据字典更难理解。明确命名每个约束有助于我们浏览物理数据库。当我们的 SQL 违反约束时，它也会导致更容易理解的错误消息。
2. 一致地命名您的约束。HISTORY_T有称为historys_T_FKand 的约束fk_order_id_orders，这两个都没有帮助。一个有用的约定是<child_table>_<parent_table>_fk. 所以history_customer_fk和history_order_fk分别。
3. 使用单独的语句创建约束会很有用。创建表然后主键然后外键将避免上面确定的依赖关系排序问题。
4. 您正在尝试在和之间创建循环外键。您可以通过在单独的语句中创建约束来做到这一点，但不要这样做：插入行时会出现问题，甚至更糟的是删除问题。您应该重新考虑您的数据模型并找到一种方法来对两个表之间的关系进行建模，以便一个是父表，另一个是子表。或者您可能需要一种不同类型的关系，例如交集表。LIBRARY_TFORMATS
5. 避免脚本中出现空行。有些工具会处理它们，但有些工具不会。我们可以配置 SQL*Plus 来处理它们，但最好避免这种需要。
6. 的命名约定LIBRARY_T是丑陋的。尝试找到一个更具表现力的名称，它不需要不必要的后缀以避免关键字冲突。
7. T_CUSTOMERS甚至更丑，既与您的其他表不一致，又完全没有必要，因为customers它不是关键字。

Naming things is hard. You wouldn't believe the wrangles I've had about table names over the years. The most important thing is consistency. If I look at a data dictionary and see tables called T_CUSTOMERSand LIBRARY_Tmy first response would be confusion. Why are these tables named with different conventions? What conceptual differencedoes this express? So, please, decide on a naming convention and stick to. Make your table names either all singular or all plural. Avoid prefixes and suffixes as much as possible; we already know it's a table, we don't need a T_or a _TAB.

命名事物是困难的。你不会相信我多年来对表名的争论。最重要的是一致性。如果我查看数据字典并看到调用的表T_CUSTOMERS，LIBRARY_T我的第一反应将是混乱。为什么这些表以不同的约定命名？这表达了什么 概念上的差异？所以，请决定一个命名约定并坚持下去。使您的表名称全部为单数或全部为复数。尽量避免前缀和后缀；我们已经知道它是一张桌子，我们不需要 aT_或 a _TAB。

回答by Luke Woodward

I would recommend separating out all of the foreign-key constraints from your CREATE TABLEstatements. Create all the tables first without FK constraints, and then create all the FK constraints once you have created the tables.

我建议将所有外键约束从您的CREATE TABLE语句中分离出来。首先创建没有 FK 约束的所有表，然后在创建表后创建所有 FK 约束。

You can add an FK constraint to a table using SQL like the following:

您可以使用 SQL 向表添加 FK 约束，如下所示：

ALTER TABLE orders ADD CONSTRAINT orders_FK
  FOREIGN KEY (m_p_unique_id) REFERENCES library (m_p_unique_id);

In particular, your formatsand librarytables both have foreign-key constraints on one another. The two CREATE TABLEstatements to create these two tables can never run successfully, as each will only work when the other table has already been created.

特别是，您的表formats和library表都彼此具有外键约束。CREATE TABLE创建这两个表的两个语句永远不会成功运行，因为每个语句只有在另一个表已经创建后才能运行。

Separating out the constraint creation allows you to create tables with FK constraints on one another. Also, if you have an error with a constraint, only that constraint fails to be created. At present, because you have errors in the constraints in your CREATE TABLEstatements, then entire table creation fails and you get various knock-on errors because FK constraints may depend on these tables that failed to create.

分离约束创建允许您创建彼此具有 FK 约束的表。此外，如果您有一个约束错误，则只有该约束无法创建。目前，因为你的CREATE TABLE语句中的约束有错误，那么整个表创建失败，你会得到各种连锁错误，因为 FK 约束可能依赖于这些未能创建的表。

回答by Michael-O

Albeit from the useless _Tand incorrectly spelled histories. If you are using SQL*Plus, it does not accept create table statements with empty new lines between create table <name> (and column definitions.

尽管来自无用_T且拼写错误的历史记录。如果您使用的是SQL*Plus，则它不接受在create table <name> (和 列定义之间具有空新行的 create table 语句。

回答by ron tornambe

Firstly, in histories_T, you are referencing table T_customer (should be T_customers) and secondly, you are missing the FOREIGN KEY clause that REFERENCES orders; which is not being created (or dropped) with the code you provided.

首先，在 history_T 中，您正在引用表 T_customer（应该是 T_customers），其次，您缺少 REFERENCES 订购的 FOREIGN KEY 子句；不是使用您提供的代码创建（或删除）的。

There may be additional errors as well, and I admit Oracle has never been very good at describing the cause of errors - "Mutating Tables" is a case in point.

可能还有其他错误，我承认 Oracle 从来都不擅长描述错误的原因——“变异表”就是一个很好的例子。

Let me know if there additional problems you are missing.

如果您遗漏了其他问题，请告诉我。