ORA-00907: missing right parenthesis

问题:

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. 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 its 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.               

回答1:

Here is a full list of the errors:

1. Foreign key constraints require us to nominated the referencing column(s) on the child table as well as the referenced column(s) on the parent table. So the foreign key declarations should look like this: CONSTRAINT fk_order_id_orders FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE
2. 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_T before you have created the referenced ORDERS table.
3. The order of components is not arbitrary. We have to declare all the columns before we declare the table level constraints. This affects both ORDERS and HISTORYS_T.
4. You have misspelled the names of the referenced tables in some of the foreign key clauses (LIBRARY_T and FORMAT_T).
5. You need to provide an expression in the DEFAULT clause. For DATE columns that is usually the current date, DATE DEFAULT sysdate.

These are all syntax errors. You would have wasted considerably less than two days on this if you had looked properly at Oracle's documentation. Find it here.

As well as errors, your scripts contain mistakes.

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. We should always explicitly name every constraint.
2. It is 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.
3. You are trying to create cyclic foreign keys between LIBRARY_T and FORMATS. You can 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.
4. The naming convention of LIBRARY_T is ugly. Try to find a more expressive name which doesn't require a needless suffix to avoid a keyword clash.
5. T_CUSTOMERS is even uglier, being both inconsistent with your other tables and completely unnecessary, as customers is not a keyword.

回答2:

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

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

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

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

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 TABLE statements, then entire table creation fails and you get various knock-on errors because FK constraints may depend on these tables that failed to create.

回答3:

Albeit from the useless _T and 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.

回答4:

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.

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.

Let me know if there additional problems you are missing.