Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2010
    Posts
    22

    Unanswered: foreign in menu table

    hi im trying to insert a foreign key in my menu table and im having trouble getting it to work.

    here's the exact code to make the menu_id a foreign key in my food_info table:

    alter table Food_Info add(constraint fk_Menu_ID FOREIGN KEY(Menu_ID) REFERENCES Menu(Menu_ID));

    if you need to know more then reply to this post.
    any help or tips are always welcome.
    thanks everyone.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    And what errors are you getting when you enter the command? Is Menu_id in food_info indexed? Is Menu_id in menu the primary key or a unique index?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    The ( after add is not allowed:
    Code:
    alter table Food_Info 
        add constraint fk_Menu_ID FOREIGN KEY(Menu_ID) REFERENCES Menu(Menu_ID);
    For future posts, please use [code] tags to make the SQL statements more readable and always include the error message you get (copy & paste it)

  4. #4
    Join Date
    Aug 2010
    Posts
    22
    @bielstwh
    this is the original error code i am getting when i try to insert the foreign key.
    [code]ORA-00904: "MENU_ID": invalid identifier[code]

    @shammat
    i removed the left bracket like you suggested and i got a different error.
    [code]ORA-01735: invalid ALTER TABLE option[code]

  5. #5
    Join Date
    Aug 2010
    Posts
    22
    I even tried to insert values into my table but it didnt work: error is saying...
    ORA-00933: SQL command not properly ended

    here's the code iv been trying:
    INSERT INTO Food_Info values
    (801, 'Garlic Bread', '£4.49', 'Fresh Italian style bread with Garlic Butter'),
    (802, 'Bread Puffs', '£3.49', 'Garnished with garlic,sour cream & shredded cheese'),
    (803, 'Zanzi Fries', '£4.50', 'Sweet potatoes coated in spices & fried golden brown'),
    (804, 'Peanut Porridge', '£2.29', 'Delicious breakfast treat'),
    (805, 'Razor Clam in Sha-Cha Sauce', '£5.99', 'Fresh clams cooked in shredded ginger & sweet seafood sauce'),
    (806, 'Muenster Cheese Soufflé', '£4.49', 'With Red Bell Pepper & Tomato Salad');

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    i removed the left bracket like you suggested and i got a different error.
    There is something you are not telling us, because the syntax is valid:

    Code:
    SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 17:52:49 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning and OLAP options
    
    SQL> create table menu (menu_id integer not null primary key);
    
    Table created.
    
    SQL> create table food_menu (menu_id integer not null);
    
    Table created.
    
    SQL> alter table food_menu
      2     add constraint fk_Menu_ID foreign key (menu_id) references menu(menu_id);
    
    Table altered.
    
    SQL>
    Please show us the full definition (as CREATE TABLE) of the Food_Info and menu tables. And the complete statement you are running.


    Btw: you need to close the code tags (using [/code], please see the online help for details:
    http://www.dbforums.com/misc.php?do=bbcode#code

    I even tried to insert values into my table but it didnt work: error is saying...
    ORA-00933: SQL command not properly ended
    You cannot use the INSERT statement like that. You need to write one INSERT per row you are inserting in Oracle.

    Please see the manual for details: http://docs.oracle.com/cd/B28359_01/...ments_9014.htm

  7. #7
    Join Date
    Aug 2010
    Posts
    22
    yes, i am trying to create a database that holds "food_info", "menu", "ethnicity", "ingredient", "purchase", "supplier_info", "supplier_product_line".

    i am trying to insert values into these tables using Oracle Apex and im having some issue with the foreign key constraints and inserting multiple values instead of inserting them one by one. they are likely to change so its easier to insert them all in one go but apparently thats not possible?

  8. #8
    Join Date
    Aug 2010
    Posts
    22
    i tried to insert another foreign key thinking it was just menu that wasnt working but i got the same error
    Code:
    alter table Food_Info add constraint fk_Ethnicity FOREIGN KEY(Ethnicity_ID) 
    REFERENCES Ethnicity(Ethnicity_ID));
    but same error

    Code:
    ORA-01735: invalid ALTER TABLE option

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Again: there is something you are not telling us. As I have shown you the syntax is correct. So it must be your SQL tool or something else.

  10. #10
    Join Date
    Aug 2010
    Posts
    22
    i'd love to know why its not working. im trying to put a menu_id foreign key in my food_info table. thats it. simple!
    but for some reason its not working, giving me 2 recurring errors?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i'd love to know why its not working.
    you have mismatched/unmatched parenthesis.
    remove/eliminate/delete the rightmost ")" & try again
    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.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    you have mismatched/unmatched parenthesis.
    remove/eliminate/delete the rightmost ")" & try again
    Good catch.
    I was so convinced ArminM would copy & paste my corrected statement, that I didn't look close enough

  13. #13
    Join Date
    Aug 2010
    Posts
    22
    regardless, it still not working. I'm going back to review my table structures and constraints.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by ArminM View Post
    regardless, it still not working. I'm going back to review my table structures and constraints.
    Did you run my sample statements in SQL*Plus?

    Which tool are you using to run the statements?

    Can you post all statements that you are running? Ideally copy the session from SQL*Plus as I did (do not post a screenshot of the SQL*Plus window pleas)

  15. #15
    Join Date
    Aug 2010
    Posts
    22
    No, I 'm using Oracle Apex to run my statements. (Oracle Apex > SQL Workshop > SQL Commands to insert my code.)

Posting Permissions

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