| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
12-12-11, 10:58
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 22
|
|
|
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.
|
|
12-12-11, 11:02
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,222
|
|
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. 
|
|
12-12-11, 11:03
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
|
|
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)
|
|
12-12-11, 11:17
|
|
Registered User
|
|
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]
|
|
12-12-11, 11:43
|
|
Registered User
|
|
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');
|
|
12-12-11, 11:54
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
|
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
Quote:
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
|
|
12-12-11, 12:09
|
|
Registered User
|
|
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?
|
|
12-12-11, 12:56
|
|
Registered User
|
|
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
|
|
12-12-11, 13:40
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
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.
|
|
12-12-11, 13:48
|
|
Registered User
|
|
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?
|
|
12-12-11, 14:01
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
>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.
|
|
12-12-11, 14:21
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by anacedent
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
|
|
12-13-11, 11:00
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 22
|
|
regardless, it still not working. I'm going back to review my table structures and constraints.
|
|
12-13-11, 11:13
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by ArminM
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)
|
|
12-13-11, 15:57
|
|
Registered User
|
|
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.)
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
foreign in menu table
