ORA-00907 missing right parenthesis

Question

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

User · Answer

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.

User · Answer

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  lt name gt    and column definitions

User · Answer

ORA-00907  missing right parenthesis   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 bracket capability  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 parenthesis and leaves it to us to spot the actual bloomer   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   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   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    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  You have misspelled the names of the referenced tables in some of the foreign key clauses  LIBRARY T and FORMAT T    You need to provide an expression in the DEFAULT clause   For DATE columns that is usually the current 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    As well as syntax errors  your scripts contain design mistakes  These are not failures  but bad practice which should not become habits    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    Name your constraints consistently  HISTORY T has constraints called historys T FK and fk order id orders  neither of which is helpful  A useful convention is  lt child table gt   lt parent table gt  fk  So history customer fk and history order fk respectively  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  You are trying to create cyclic foreign keys between LIBRARY T and 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  Avoid blank lines in your scripts  Some tools will handle them but some will not  We can configure SQL Plus to handle them but it s better to avoid the need  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  T CUSTOMERS is even uglier  being both inconsistent with your other tables and completely unnecessary  as customers is not a keyword    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 CUSTOMERS and LIBRARY T my first response would be confusion  Why are these tables named with different conventions  What  conceptual difference does 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

User · Answer

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

[sql] ORA-00907: missing right parenthesis

Examples related to sql

Examples related to oracle

Examples related to ora-00907