Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    32

    Unanswered: my schema doesnt work for foreign key tables...

    Hi. Can someone please look at my code and tell me why my schema file isnt working when I try to create the tables in Oracle? It will create the tables, but when I try to add the foreign keys, it doesnt work, saying something like "missing right parenthesis".. it could be that im just not writing the code right, as im fairly new to writing oracle schema files.

    Many thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    My car is not working.
    Please tell me how to fix it.

    Error? What Error? I don't see any error?

    Use CUT & PASTE of whole SQL*Plus session to show exactly what you did & how Oracle responded.
    Plus use "code tags" as describe in STICKY post at top of forum.
    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.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by lockmac
    saying something like "missing right parenthesis"
    Well that simply tells, that you have some kind of error in your SQL. Steps to find the error:

    1) read the manual
    2) run each statement one by one to find the incorrect statement
    3) compare your syntax with the one from the manual

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, the error message is misleading

    you have 59 left parentheses, and 59 right parentheses

    but you are missing a lot of commas inside your CREATE TABLE statements

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2007
    Posts
    32
    Thanks for the replies.. i have done many hours of reading how to do the foreign key constraints and am fairly sure they are correct... here is what oracle tells me... and where am i missing some commas? i had a look and i thought they were all their

    Code:
    TABLE_NAME                     TABLE_TYPE  
    ------------------------------ ----------- 
    BIN$NnPpas9KYDTgRAADuk3XZA==$0 TABLE       
    BIN$NnPpas9NYDTgRAADuk3XZA==$0 TABLE       
    BIN$NnPpas9SYDTgRAADuk3XZA==$0 TABLE       
    
    3 rows selected
    
    CREATE TABLE succeeded.
    CREATE TABLE succeeded.
    CREATE TABLE succeeded.
    CREATE TABLE succeeded.
    CREATE TABLE succeeded.
    
    Error starting at line 51 in command:
    CREATE TABLE TITLE 
    (
      titleID NUMERIC NOT NULL,
      ratingID INTEGER NOT NULL,
      directorID INTEGER NOT NULL,
      title_name VARCHAR(30) NOT NULL,
      number_discs NUMERIC NOT NULL,
      synopsis VARCHAR(150) NOT NULL,
      production_year NUMERIC NOT NULL,
      release_date DATE NOT NULL,
      studio VARCHAR(30) NOT NULL,
      running_time NUMERIC NOT NULL,
      date_added DATE NOT NULL,
      added_by_username VARCHAR(15),
    CONSTRAINT title_pk PRIMARY KEY(titleID)
    CONSTRAIT rating_fk FOREIGN KEY (ratingID) REFERENCES RATING(ratingID)
    CONSTRAIT director_fk FOREIGN KEY (directorID) REFERENCES DIRECTOR(directorID)
    )
    Error at Command Line:65 Column:40
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    
    Error starting at line 72 in command:
    CREATE TABLE CUSTOMER 
    (
      memberID NUMERIC NOT NULL,
      cityID NUMERIC NOT NULL,
      customer_name VARCHAR(30) NOT NULL,
      date_joined NUMERIC NOT NULL,
      phone_number VARCHAR(10) NOT NULL,
      number_rentals NUMERIC,
      street_address INTEGER,
    CONSTRAINT customer_pk PRIMARY KEY(memberID)
    CONSTRAIT city_fk FOREIGN KEY (cityID) REFERENCES CITY(cityID)
    )
    Error at Command Line:81 Column:44
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    
    Error starting at line 86 in command:
    CREATE TABLE CUSTOMERTITLE 
    (
      memberID NUMERIC NOT NULL,
      date_rented DATE,
      return_date DATE,
    CONSTRAINT customertitle_pk PRIMARY KEY(memberID)
    CONSTRAIT member_fk FOREIGN KEY (memberID) REFERENCES CUSTOMER(memberID)
    )
    Error at Command Line:91 Column:49
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    
    Error starting at line 96 in command:
    CREATE TABLE COPY 
    (
      copyID NUMERIC NOT NULL,
      memberID NUMERIC NOT NULL,
      date_purchased DATE,
      purchase_price NUMERIC,
      times_rented NUMERIC,
    CONSTRAINT copy_pk PRIMARY KEY(copyID)
    CONSTRAIT member_fk FOREIGN KEY (memberID) REFERENCES CUSTOMER(memberID)
    )
    Error at Command Line:103 Column:38
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    
    Error starting at line 110 in command:
    CREATE TABLE TITLEACTOR 
    (
      actorID NUMERIC NOT NULL,
      titleID NUMERIC NOT NULL,
    CONSTRAINT titleactor_pk PRIMARY KEY(actorID, titleID)
    CONSTRAIT acotr_fk FOREIGN KEY (actorID) REFERENCES ACTOR(actorID)
    CONSTRAIT title_fk FOREIGN KEY (titleID) REFERENCES TITLE(titleID)
    )
    Error at Command Line:114 Column:54
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    
    Error starting at line 120 in command:
    CREATE TABLE TITLECOPY 
    (
      titleID NUMERIC NOT NULL,
      copyID NUMERIC NOT NULL,
    CONSTRAINT titlecopy_pk PRIMARY KEY(titleID, copyID)
    CONSTRAIT title_fk FOREIGN KEY (titleID) REFERENCES TITLE(titleID)
    CONSTRAIT copy_fk FOREIGN KEY (copyID) REFERENCES COPY(copyID)
    )
    Error at Command Line:124 Column:52
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    
    Error starting at line 130 in command:
    CREATE TABLE TITLEGENRE 
    (
      genreID NUMERIC NOT NULL,
      titleID NUMERIC NOT NULL,
    PRIMARY KEY(genreID, titleID)
    CONSTRAIT genre_fk FOREIGN KEY (genreID) REFERENCES GENRE(genreID)
    CONSTRAIT title_fk FOREIGN KEY (titleID) REFERENCES TITLE(titleID)
    )
    Error at Command Line:134 Column:29
    Error report:
    SQL Error: ORA-00907: missing right parenthesis
    00907. 00000 -  "missing right parenthesis"
    *Cause:    
    *Action:
    Thanks guys

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CREATE TABLE TITLE 
    (
      titleID NUMERIC NOT NULL,
      ratingID INTEGER NOT NULL,
      directorID INTEGER NOT NULL,
      title_name VARCHAR(30) NOT NULL,
      number_discs NUMERIC NOT NULL,
      synopsis VARCHAR(150) NOT NULL,
      production_year NUMERIC NOT NULL,
      release_date DATE NOT NULL,
      studio VARCHAR(30) NOT NULL,
      running_time NUMERIC NOT NULL,
      date_added DATE NOT NULL,
      added_by_username VARCHAR(15),
    CONSTRAINT title_pk PRIMARY KEY(titleID)
    /* comma missing here */
    CONSTRAIT /* spelling mistake */ rating_fk FOREIGN KEY (ratingID) REFERENCES RATING(ratingID)
    /* comma missing here */
    CONSTRAIT /* spelling mistake */ director_fk FOREIGN KEY (directorID) REFERENCES DIRECTOR(directorID)
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that you'll have to spend some more hours on this.

    i had a look and i thought they were all their (should be 'there', I guess?)
    So how come Rudy found those errors and you didn't? Are you pulling people's legs here?

  8. #8
    Join Date
    Aug 2007
    Posts
    32
    Do you honestly think im pulling peoples legs... that i have nothing better to do than post fake schema files on the internet just to get a laugh? Don't get me wrong, im grateful for the help, but what had happened was that was an error i had fixed a while ago, but was working off a backed up copy of my work, so i thought i had fixed that error but it was obiously still present.

    cheers

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Look, we have no idea what you are doing. All we know is information you provide, nothing more. The rest is just guessing. So how is anyone supposed to know you used backup files which are incorrect?

    OK, never mind; did you, finally, make it work?

  10. #10
    Join Date
    Aug 2007
    Posts
    32
    i know.. and im just saying.. do you realy think im pulling your leg..

    and yes i did finally make it work thanks.. have a good one

Posting Permissions

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