LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 06-12-2012, 04:06 PM   #1
rootaccess
Member
 
Registered: Mar 2012
Posts: 211

Rep: Reputation: Disabled
Insert into FOREIGN KEY field in a MySQL table


Hello. Been trying to insert into FK field with no luck.

I created two tables with a script and loaded them:


Code:
use database;
CREATE TABLE coca_cola_customers (
customer_id INT unsigned NOT NULL AUTO_INCREMENT,
customer_name char(30) NOT NULL,
contact_name char(30) NOT NULL,
PRIMARY KEY (customer_id)
) ENGINE = INNODB;
 
CREATE TABLE coca_cola_orders (
order_id INT unsigned NOT NULL AUTO_INCREMENT, 
order_date char(10) NOT NULL,
customer_id INT unsigned NOT NULL,
PRIMARY KEY (order_id),
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES coca_cola_customers (customer_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = INNODB;
I then inserted some random info in my coca_cola_customers table
Here is exactly what I inserted:

Code:
INSERT INTO coca_cola_customers VALUES (NULL,'McDonalds','Ray Fisher','(555)123-4567');
INSERT INTO coca_cola_customers VALUES (NULL,"Domino's Pizza",'Jason Smith','(555)987-4567');
INSERT INTO coca_cola_customers VALUES (NULL,"Mountain Fitness",'Steve Kemp','(555)984-1234')
And that worked nicely for the coca_cola_customers table

Now I want to add information to the coca_cola_orders table
When I try to run a simple INSERT INTO command:

INSERT INTO coca_cola_orders VALUES (NULL,'06/11/2012','I GET STUCK RIGHT HERE);

Its probably a lot more complicated than I expect but thats ok. I just want to understand what I need to do in order to get my customer_id from coca_cola_customers to join in customer_id to coca_cola_orders.

I know about joining and have outer joins as well and have created tables and experimented with them successfully but this looks like something different.

I am using mysql straight from the command line for all this
Would appreciate any help.

Thanks,
Shawn
 
Old 06-12-2012, 04:18 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,067
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Not sure I understand your problem ... all you need to do is enter
a valid customer ID where you have "I GET STUCK RIGHT HERE" ... this
is an insert, and as this field is the only relation to your customers
there's no way to populate it magically.



Cheers,
Tink
 
Old 06-12-2012, 04:26 PM   #3
rootaccess
Member
 
Registered: Mar 2012
Posts: 211

Original Poster
Rep: Reputation: Disabled
I just figured out my mistake. I kept thinking I needed to JOIN those columns but thats not the purpose of that table. The purpose is to CREATE that table like from a web site form, now it all makes sense.

I can only input 1, 2 or 3 in that field since I only have those order_id number's from my first table.

Thanks for your help!

Quote:
Originally Posted by Tinkster View Post
Not sure I understand your problem ... all you need to do is enter
a valid customer ID where you have "I GET STUCK RIGHT HERE" ... this
is an insert, and as this field is the only relation to your customers
there's no way to populate it magically.



Cheers,
Tink
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] mySQL Foreign key that will not be constrained Mogget Linux - Server 1 10-11-2011 06:01 AM
Python MySQL: clean multiple foreign keys table threaderslash Programming 0 11-28-2009 04:44 AM
Can not insert word file into mysql table (in blob field) prabhatsoni Linux - Software 2 07-21-2006 05:01 AM
How to insert same text in one field for 10,000 records table edhan Linux - Newbie 6 06-13-2005 03:48 AM
Mysql Foreign Key munna_502 Linux - Software 0 06-12-2004 04:31 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 12:13 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration