Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: Creating Tables (Missing right parenthesis)

    Hi,

    I'm trying to create some tables in Oracle but when I run it says I'm missing a right parenthesis, somewhere around the REFERENCES part. It's my first time working with REFERENCES and was wondering if someone could help me out?

    Here's the code

    Code:
    CREATE TABLE rota(
    	theDate VARCHAR(10) NOT NULL, 
    	Shift VARCHAR2(30) NOT NULL, 
    	Manager NUMBER(6) NOT NULL, 
    		REFERENCES staff(Staff_Code)
    	Operator_Code NUMBER(6) NOT NULL, 
    		REFERENCES staff(Staff_Code)
    	Engineer1 NUMBER(6),
    		REFERENCES staff(Staff_Code)
    	Engineer2 NUMBER(6),
    		REFERENCES staff(Staff_Code)
    	PRIMARY KEY (theDate, Shift)
    );
    Any help would be great! Thank you!
    Last edited by Lottie; 11-01-11 at 09:33.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Lottie View Post
    ... somewhere around the REFERENCES part.
    it's the extra commas, remove those and try again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Lottie View Post
    It's my first time working with REFERENCES and was wondering if someone could help me out?
    What about consulting Oracle documentation? CREATE TABLE statement is described in SQL Language book, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

    For 11gR2 (the latest version), it is described here:
    CREATE TABLE statement: http://download.oracle.com/docs/cd/E...2.htm#i2095331
    constraint clause: http://download.oracle.com/docs/cd/E...2.htm#g1053592
    Realize the difference between inline (part of the column definition) and out-of-line (part of the table definition) constraints. Stating inline constraint syntax in place of out-of-line constraint is not supported - you should pick one of these approaches.

  4. #4
    Join Date
    Nov 2011
    Posts
    2
    Quote Originally Posted by r937 View Post
    it's the extra commas, remove those and try again
    I tried it without the extra commas the first time and it still said the right parenthesis is missing, so I added them to see if maybe that was the problem but with or without them it still throws up this error. I'm using Oracle 10g

    Flyboy - I tried that but I couldn't make sense of it and was hoping someone hear could maybe explain what the problem was a little better.
    Last edited by Lottie; 11-01-11 at 09:19.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Lottie View Post
    Flyboy - I tried that but I couldn't make sense of it and was hoping someone hear could maybe explain what the problem was a little better.
    Tried what? Read and understand the documentation? It is a pity; there are also examples in the bottom of that page. Another code attempts? As you did not post even your Oracle version, it is impossible to suggest more.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    And please post the complete CREATE TABLE statement. The one you posted is not valid anyway.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have an open paren after rota, where is the closing paren at the end of the command.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by beilstwh View Post
    You have an open paren after rota, where is the closing paren at the end of the command.
    it's right here...
    Quote Originally Posted by Lottie View Post
    Code:
    CREATE TABLE rota(
    	theDate VARCHAR(10) NOT NULL, 
    	Shift VARCHAR2(30) NOT NULL, 
    	Manager NUMBER(6) NOT NULL, 
    		REFERENCES staff(Staff_Code)
    	Operator_Code NUMBER(6) NOT NULL, 
    		REFERENCES staff(Staff_Code)
    	Engineer1 NUMBER(6),
    		REFERENCES staff(Staff_Code)
    	Engineer2 NUMBER(6),
    		REFERENCES staff(Staff_Code)
    	PRIMARY KEY (theDate, Shift)
    ); -- IT'S RIGHT HERE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Foreign key - Oracle FAQ

    shows proper syntax
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You are mixing the inlide referential constraints syntax with the out-of-line syntax.

    When using the in-line syntax that is part of the column definition the comma that delimits the columns needs to go after the references clause not between the column definition and the (inline) references clause.

    You only need the comma in front of the references clause if you use an out-of-line constraint at the end of the table.

    So your create table should look like this:
    Code:
    CREATE TABLE rota 
    (
      theDate         VARCHAR(10) NOT NULL,
      Shift           VARCHAR2(30) NOT NULL,
      Manager         NUMBER(6) NOT NULL REFERENCES staff (staff_code),
      Operator_Code   NUMBER(6) NOT NULL REFERENCES staff (staff_code),
      Engineer1       NUMBER(6) REFERENCES staff (staff_code),
      Engineer2       NUMBER(6) REFERENCES staff (staff_code),
      PRIMARY KEY (theDate, Shift)
    );
    You should really try to understand the syntax diagrams in the manual.
    They contain all the information you need.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •