Mixing
INSERT/ UPDATE Statement conflicted with the foreign key constraint
I've been working on a database migration going from Access db -> Azure SQL server.
I have recreated all the OleDbCommand to SqlCommand. For example...
private bool WriteCustomerComment(int customerID, string comment)
{
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (?, ?)";
var parameters = new object
{
customerID, comment
};
return ExecuteNonQuery(sqlQuery, parameters);
}
Has become...
private bool WriteCustomerComment(int customerID, string comment)
{
var parameterList = new List<SqlParameter>();
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (@customerId, @comment)";
parameterList.Add(new SqlParameter("@customerId", customerID));
parameterList.Add(new SqlParameter("@comment", comment));
return ExecuteNonQuery(sqlQuery, parameterList);
}
Now. All of the program worked just fine, but I then tried to do a migration with "SQL Server Migration Assistant" (From Microsoft, created to handle access to SQL migrations).
Because migrating with the built-in "Microsoft SQL Server Management Studio" did not transfer
*Primary keys
*Foreign keys
*Table Bindings
*Constraints, etc..
When I did this it transferred all of those stated above. However, the program started having issues when i tried it out again. When I input the values into the fields required it instead gave me the following...
The INSERT Statement conflicted with the Foreign Key constraint "Sample$CustomerSample". Table dbo.Customer, Column "CustomerID".
This error message was different regarding to what kind of test i was creating. The statement could also be
The UPDATE statement conflicted with the Foreign Key constraint.....
Now I have tried getting some information online, but I dont really know how to approach this problem. Would this be some kind of script/ alter in the regarded tables? Or is it an issue in the migration? What I have found tells me that keys, etc should behave the same way both in access and SQL?
Here is a picture of the Customer table.
c# sql ms-access database-migration
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
add a comment |
I've been working on a database migration going from Access db -> Azure SQL server.
I have recreated all the OleDbCommand to SqlCommand. For example...
private bool WriteCustomerComment(int customerID, string comment)
{
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (?, ?)";
var parameters = new object
{
customerID, comment
};
return ExecuteNonQuery(sqlQuery, parameters);
}
Has become...
private bool WriteCustomerComment(int customerID, string comment)
{
var parameterList = new List<SqlParameter>();
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (@customerId, @comment)";
parameterList.Add(new SqlParameter("@customerId", customerID));
parameterList.Add(new SqlParameter("@comment", comment));
return ExecuteNonQuery(sqlQuery, parameterList);
}
Now. All of the program worked just fine, but I then tried to do a migration with "SQL Server Migration Assistant" (From Microsoft, created to handle access to SQL migrations).
Because migrating with the built-in "Microsoft SQL Server Management Studio" did not transfer
*Primary keys
*Foreign keys
*Table Bindings
*Constraints, etc..
When I did this it transferred all of those stated above. However, the program started having issues when i tried it out again. When I input the values into the fields required it instead gave me the following...
The INSERT Statement conflicted with the Foreign Key constraint "Sample$CustomerSample". Table dbo.Customer, Column "CustomerID".
This error message was different regarding to what kind of test i was creating. The statement could also be
The UPDATE statement conflicted with the Foreign Key constraint.....
Now I have tried getting some information online, but I dont really know how to approach this problem. Would this be some kind of script/ alter in the regarded tables? Or is it an issue in the migration? What I have found tells me that keys, etc should behave the same way both in access and SQL?
Here is a picture of the Customer table.
c# sql ms-access database-migration
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
2
What customerID did you try to insert and do you know that that id exists in the customer table? (Your error message seems to clearly indicate that you're trying to create a comment for a customer that doesn't exist, which your constraint correctly prohibits.). (You should also be looking at the definition of thecustomerCommentstable, that's the table which has the constraint on it, it just references thecustomertable.)
– MatBailie
Nov 22 '18 at 9:41
add a comment |
I've been working on a database migration going from Access db -> Azure SQL server.
I have recreated all the OleDbCommand to SqlCommand. For example...
private bool WriteCustomerComment(int customerID, string comment)
{
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (?, ?)";
var parameters = new object
{
customerID, comment
};
return ExecuteNonQuery(sqlQuery, parameters);
}
Has become...
private bool WriteCustomerComment(int customerID, string comment)
{
var parameterList = new List<SqlParameter>();
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (@customerId, @comment)";
parameterList.Add(new SqlParameter("@customerId", customerID));
parameterList.Add(new SqlParameter("@comment", comment));
return ExecuteNonQuery(sqlQuery, parameterList);
}
Now. All of the program worked just fine, but I then tried to do a migration with "SQL Server Migration Assistant" (From Microsoft, created to handle access to SQL migrations).
Because migrating with the built-in "Microsoft SQL Server Management Studio" did not transfer
*Primary keys
*Foreign keys
*Table Bindings
*Constraints, etc..
When I did this it transferred all of those stated above. However, the program started having issues when i tried it out again. When I input the values into the fields required it instead gave me the following...
The INSERT Statement conflicted with the Foreign Key constraint "Sample$CustomerSample". Table dbo.Customer, Column "CustomerID".
This error message was different regarding to what kind of test i was creating. The statement could also be
The UPDATE statement conflicted with the Foreign Key constraint.....
Now I have tried getting some information online, but I dont really know how to approach this problem. Would this be some kind of script/ alter in the regarded tables? Or is it an issue in the migration? What I have found tells me that keys, etc should behave the same way both in access and SQL?
Here is a picture of the Customer table.
c# sql ms-access database-migration
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
I've been working on a database migration going from Access db -> Azure SQL server.
I have recreated all the OleDbCommand to SqlCommand. For example...
private bool WriteCustomerComment(int customerID, string comment)
{
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (?, ?)";
var parameters = new object
{
customerID, comment
};
return ExecuteNonQuery(sqlQuery, parameters);
}
Has become...
private bool WriteCustomerComment(int customerID, string comment)
{
var parameterList = new List<SqlParameter>();
var sqlQuery = "INSERT INTO CustomerComment (CustomerID, Comment) VALUES (@customerId, @comment)";
parameterList.Add(new SqlParameter("@customerId", customerID));
parameterList.Add(new SqlParameter("@comment", comment));
return ExecuteNonQuery(sqlQuery, parameterList);
}
Now. All of the program worked just fine, but I then tried to do a migration with "SQL Server Migration Assistant" (From Microsoft, created to handle access to SQL migrations).
Because migrating with the built-in "Microsoft SQL Server Management Studio" did not transfer
*Primary keys
*Foreign keys
*Table Bindings
*Constraints, etc..
When I did this it transferred all of those stated above. However, the program started having issues when i tried it out again. When I input the values into the fields required it instead gave me the following...
The INSERT Statement conflicted with the Foreign Key constraint "Sample$CustomerSample". Table dbo.Customer, Column "CustomerID".
This error message was different regarding to what kind of test i was creating. The statement could also be
The UPDATE statement conflicted with the Foreign Key constraint.....
Now I have tried getting some information online, but I dont really know how to approach this problem. Would this be some kind of script/ alter in the regarded tables? Or is it an issue in the migration? What I have found tells me that keys, etc should behave the same way both in access and SQL?
Here is a picture of the Customer table.
c# sql ms-access database-migration
c# sql ms-access database-migration
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
edited Nov 22 '18 at 13:06
E_net4
12.3k63669
12.3k63669
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
asked Nov 22 '18 at 9:31
FilipE92FilipE92
197
197
2
What customerID did you try to insert and do you know that that id exists in the customer table? (Your error message seems to clearly indicate that you're trying to create a comment for a customer that doesn't exist, which your constraint correctly prohibits.). (You should also be looking at the definition of thecustomerCommentstable, that's the table which has the constraint on it, it just references thecustomertable.)
– MatBailie
Nov 22 '18 at 9:41
add a comment |
2
What customerID did you try to insert and do you know that that id exists in the customer table? (Your error message seems to clearly indicate that you're trying to create a comment for a customer that doesn't exist, which your constraint correctly prohibits.). (You should also be looking at the definition of thecustomerCommentstable, that's the table which has the constraint on it, it just references thecustomertable.)
– MatBailie
Nov 22 '18 at 9:41
2
2
What customerID did you try to insert and do you know that that id exists in the customer table? (Your error message seems to clearly indicate that you're trying to create a comment for a customer that doesn't exist, which your constraint correctly prohibits.). (You should also be looking at the definition of the
customerComments table, that's the table which has the constraint on it, it just references the customer table.)– MatBailie
Nov 22 '18 at 9:41
What customerID did you try to insert and do you know that that id exists in the customer table? (Your error message seems to clearly indicate that you're trying to create a comment for a customer that doesn't exist, which your constraint correctly prohibits.). (You should also be looking at the definition of the
customerComments table, that's the table which has the constraint on it, it just references the customer table.)– MatBailie
Nov 22 '18 at 9:41
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53427734%2finsert-update-statement-conflicted-with-the-foreign-key-constraint%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53427734%2finsert-update-statement-conflicted-with-the-foreign-key-constraint%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
What customerID did you try to insert and do you know that that id exists in the customer table? (Your error message seems to clearly indicate that you're trying to create a comment for a customer that doesn't exist, which your constraint correctly prohibits.). (You should also be looking at the definition of the
customerCommentstable, that's the table which has the constraint on it, it just references thecustomertable.)– MatBailie
Nov 22 '18 at 9:41