[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using INSERT in multiple tables that are linked with a Primary Key

Posted on 2007-10-13
8
Medium Priority
?
2,463 Views
Last Modified: 2013-12-24
I am new to SQL and need to figure out how to insert coulmns into two tables that are linked by a primary key.

I tried:
 INSERT INTO tableone(data1, data2, data,3)
             VALUES (1,2,3);
 INSERT INTO tabletwo(data1, data4, data,5)
             VALUES (1,7,8)
This only inserts the data in tableone. table two errors out with a primary key error.

Any help is much appreciated.
0
Comment
Question by:RETEZE
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20071208
what IS the primary key in tabletwo?
0
 

Author Comment

by:RETEZE
ID: 20071507
The feild Data1
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20071637
what does the following query return:
SELECT * FROM tabletwo WHERE data1 = 1

is data1 really a primary key, or should it not rather be a foreign key, pointing to the primary key in tableone?
I ask, because you said:
> two tables that are linked by a primary key.
in which case, having a primary key on tabletwo would ONLY make sense if the relationship is ONE-to-ONE...
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:RETEZE
ID: 20072114
I must be wrong in my thinking because there are no results for the query you gave me. The Primary key is in tableone and it is not data1 it is another field called "header"

I am trying to insert into the tabletwo and tableone at the same time. My situation is I have one table that has the customer infomation and the second table has their products.

If I use:
 INSERT INTO tableone(data1, data2, data,3)
             VALUES (1,2,3);
I get the data in the first table., but I do not know how to get the data in the second table. I don't know how to get he primary key from tableone to be inserted/linked into tabletwo.

Thanks for the help so far.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20073043
ok, after the first insert, if you run this:

 INSERT INTO tabletwo(data1, data4, data,5)
             VALUES (1,7,8)

can you post the EXACT error message, please?

0
 

Author Comment

by:RETEZE
ID: 20073682
The error is:

 SQL(-691) : Missing key in referenced table for referential constraint [ISAM error -111:no record found.]
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20074450
can you show the syntax you used to create the foreign key constraint on the table?
what about putting a commit before that insert statement?

0
 

Author Comment

by:RETEZE
ID: 20077956
Found a second constraint. Thanks,
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question