FK Constraint Violation upon INSERT

[Dave]

I have an adp application that was automatically generated upon an upsizing
of an Access database to SQL Server 2000.

For the most part everything went remarkably well. However, I just
encountered an error when I tried to add a new record.

The error is: INSERT Statement conflicted with Foreign Key constraint
"solution_FK00" The conflict occurred in database x, table "question"
column "Questionid"

This error occus when I am trying to insert a recod in the parent table;
like inserting a record in the Orders table without a correposnding record
in Order_Detail table..

Normally this would not cause a referential integrity problem. So I am
unclear on why I am getting the error.

 

[Dave]

As a followup, let me clarify that I am using the Access form operations to
do my data modificaion. (i.e. there is no VBA code behind)

Here is the schema fo9r the affected tables.


CREATE TABLE [dbo].[question] (
[QuestionID] [int] IDENTITY (1, 1) NOT NULL ,
[Status] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer_Image] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hint_Image] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HP12C_Image] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HP12C_Image2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceID] [int] NULL ,
[PageNo] [int] NULL ,
[SourceQuestionNo] [int] NULL ,
[LevelID] [int] NULL ,
[Issue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Compound] [bit] NULL ,
[CreateDate] [datetime] NULL ,
[UpdateDate] [datetime] NULL ,
[Descripton] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ToDo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Text] [bit] NULL ,
[HP1] [bit] NULL ,
[HP2] [bit] NULL ,
[Excel] [bit] NULL ,
[Hint] [bit] NULL ,
[Question] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AnswerDetail] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HP12C] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LevelNote] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[solution] (
[solutionid] [int] IDENTITY (1, 1) NOT NULL ,
[questionid] [int] NULL ,
[solution] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[question] ADD
CONSTRAINT [DF__question__Status__33D4B598] DEFAULT (1) FOR [Status],
CONSTRAINT [DF__question__Source__34C8D9D1] DEFAULT (5) FOR [SourceID],
CONSTRAINT [DF__question__LevelI__35BCFE0A] DEFAULT (1) FOR [LevelID],
CONSTRAINT [DF__question__Compou__36B12243] DEFAULT (0) FOR [Compound],
CONSTRAINT [DF__question__Create__37A5467C] DEFAULT (getdate()) FOR
[CreateDate],
CONSTRAINT [DF__question__Text__38996AB5] DEFAULT (0) FOR [Text],
CONSTRAINT [DF__question__HP1__398D8EEE] DEFAULT (0) FOR [HP1],
CONSTRAINT [DF__question__HP2__3A81B327] DEFAULT (0) FOR [HP2],
CONSTRAINT [DF__question__Excel__3B75D760] DEFAULT (0) FOR [Excel],
CONSTRAINT [DF__question__Hint__3C69FB99] DEFAULT (0) FOR [Hint],
CONSTRAINT [aaaaaquestion_PK] PRIMARY KEY NONCLUSTERED
(
[QuestionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[solution] ADD
CONSTRAINT [DF__solution__questi__46E78A0C] DEFAULT (0) FOR [questionid],
CONSTRAINT [aaaaasolution_PK] PRIMARY KEY NONCLUSTERED
(
[solutionid]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[question] ADD
CONSTRAINT [FK_question_level] FOREIGN KEY
(
[LevelID]
) REFERENCES [dbo].[level] (
[LevelID]
),
CONSTRAINT [FK_question_source] FOREIGN KEY
(
[SourceID]
) REFERENCES [dbo].[source] (
[SourceID]
)
GO

ALTER TABLE [dbo].[solution] ADD
CONSTRAINT [solution_FK00] FOREIGN KEY
(
[questionid]
) REFERENCES [dbo].[question] (
[QuestionID]
)
GO

---------------------------

And here is the record source for the form::

SELECT question.QuestionID, question.Answer, question.Answer_Image,
question.Hint_Image, question.HP12C_Image, question.HP12C_Image2,
question.SourceID, question.PageNo,
question.SourceQuestionNo, question.UpdateDate, question.Question,
question.AnswerDetail, solution.solution,
question.HP12C, question.Compound, question.LevelID,
question.LevelNote, question.Issue
FROM question INNER JOIN
solution ON question.QuestionID = solution.questionid;

 

[Vadim Rapp]

D> This error occus when I am trying to insert a recod in the parent table;
D> like inserting a record in the Orders table without a correposnding
D> record in Order_Detail table..

The recordsource of your form is join of two tables, Question and Solution.
If you want to update Question, then make sure that form's property Unique
Table equals Question .

Vadim Rapp

 

[Charles Wang[MSFT]]

Hi Dave,
From your FK constraint definition statement:
ALTER TABLE [dbo].[solution] ADD
CONSTRAINT [solution_FK00] FOREIGN KEY
(
[questionid]
) REFERENCES [dbo].[question] (
[QuestionID]
)
GO

We can find that your solution table references your QUESTION table by
QUESTIONID. When you perform INSERT/UPDATE on your SOLUTION table, it is
required to obey the rule the inserted/updated QUESTIONID value must be
existed in your QUESTION table, otherwise the operation will fail. To work
around this issue, you need to first insert the related QUESTIONID to your
QUESTION table and then insert the record into your SOLUTION table.

If you have any other questions or concerns, please feel free to let us
know. Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Hi Dave,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.

Sincerely,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Dave]

Thanks Charles

Yes, I see what you are saying and understand the issue now.

This worked in Access with the mdb file but then broke when I upsized to SQL
Server and autoconverted to ADP.

It looks like the upsizing will require me to recode most of my forms to add
specific data modification logic in code modules.

IOW, once I move beyond the mdb file to SQL Server, I loose some of my
integrated form functionality which I must replace by coding myself in code
modules.

In general, is this true?

 

[Charles Wang[MSFT]]

Hi Dave,
As far as I know, if the relationship has been defined in an Access
database, it is also not allowed to insert a record to the foreign table if
the FK value does not exist in the primary table.
Considering the following Access tables with the relationship CITY.CITY_ID
= PRODUCER.CITY_ID with "Enforce Referential Integrity" checked:
CREATE TABLE [CITY]
(
CITY_ID NUMBER,
CITY_NAME TEXT
)

CREATE TABLE [PRODUCER]
(
PRODUCER_ID NUMBER,
PRODUCER_NAME TEXT,
CITY_ID NUMBER
)

When you insert a record such as (1, 'Test', 10) into PRODUCER table, if
the CITY_ID 10 is not existed in CITY table, the INSERT operation will
fail. However you said that it worked fine in your original Access database
file, so I think that the option "Enforce Referential Integrity" was not
checked in your original .mdb file. When you upsized your Access database
to SQL Server, you also selected the relationships for upsizing, and then
after the upsizing finished, the relationships were converted to FK
CONSTRAINTS in SQL Server. In this case when you tried to add a new record
which did not obey the FK Constraints, the error occurred. To eliminate the
impact, you may try re-upsizing your database to SQL Server database
without selecting "Table relationships" during the upsizing wizard. Then
you can keep your original data modification logic in code modules.

Please feel free to let me know if you have any other questions or
concerns. Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Vadim Rapp]

D> IOW, once I move beyond the mdb file to SQL Server, I loose some of my
D> integrated form functionality which I must replace by coding myself in
D> code modules.
D> In general, is this true?

you lose functionality that refers to tables and queries, because in ADP
they are no longer under Access control. But references to other forms stay.
For example, a textfield with controlsource "=tables!mytable!col1" won't
work , but "=forms!form1!field1" will.

Vadim Rapp

 

[Charles Wang[MSFT]]

Hi Dave,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Dave]

You are correct. The operation would not work prior to upgrade if the
enforce referential integrity was selected.

But I am still struggling with the migration.

In my ADP file I have a parent form that references a sub form.

The recordsource for the parent form is the "Question" table that includes
the QuestionID identity field.

The recordseource for the child table is this SQL query:

SELECT keyword.keyword, keyword_question.keywordid,
keyword_question.questionid, keyword_question.note
FROM keyword INNER JOIN keyword_question ON keyword.keywordid =
keyword_question.keywordid

The link child and master key fields are questionid.

So what I am trying to maintain in the form is a many to many relationship
bewteen Question and Keyword tables.

When I try to add a keyword to a question, I make a selection in a cbo with
the keywordid as the control source and then I type some text in the note
textbox.

As soon as I move off the textbox to the next record I get a dialog box that
states: "key value for this row was changed or deleted at the data store.
The local row is now deleted."

I click OK to close the dialog box but now when I try to move to any field
on the form I get anoither dialog box that says "Multi-step operation
generated errors. Check each status value."

I must close the form which then triggers the following error "Another user
or application has deleted this record or changed the value of the primary
key."

I then get repeated "Multi-step operation generated errors. Check each
status value." messages until I can finally reopen the form where I see that
the record I original tried to create was in fact actually created.

What could be causing this?

I am simply trying to INSERT a record into the question_keyword table (see
table def below).

There are no triggers on any of the tables involved (Question,
Question_keyword, and keyword). And there are no cascades on the FKs.

Does anyone have any idea? It worked fine before the upsizing.

Thanks
Dave


CREATE TABLE [dbo].[keyword_question] (
[keywordquestionid] [int] IDENTITY (1, 1) NOT NULL ,
[keywordid] [int] NULL ,
[questionid] [int] NULL ,
[note] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createdate] [datetime] NULL
) ON [PRIMARY]
GO

 

[Sylvain Lafontaine]

Set the UniqueTable and the ResyncCommand properties on the subform and you
should be OK. For the UniqueTable, it should be keyword_question; for the
resynccommand, set it to « MyResyncSP ? » with MyResyncSP as:

create procedure dbo.MyResyncSP (@keywordquestionid int)
as
Set NoCount ON

SELECT keyword.keyword, keyword_question.keywordid,
keyword_question.questionid, keyword_question.note
FROM keyword INNER JOIN keyword_question ON keyword.keywordid =
keyword_question.keywordid
Where keyword_question.keywordquestionid = @keywordquestionid
go

Won't be a bad idea to use aliases for the tables:

SELECT k.keyword, kq.keywordid, kq.questionid, kq.note
FROM keyword k INNER JOIN keyword_question kq ON k.keywordid = kq.keywordid
Where kq.keywordquestionid = @keywordquestionid

Take a look with the SQL-Server Profiler to make sure that everything is OK.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

You are correct. The operation would not work prior to upgrade if the
enforce referential integrity was selected.

But I am still struggling with the migration.

In my ADP file I have a parent form that references a sub form.

The recordsource for the parent form is the "Question" table that includes
the QuestionID identity field.

The recordseource for the child table is this SQL query:

SELECT keyword.keyword, keyword_question.keywordid,
keyword_question.questionid, keyword_question.note
FROM keyword INNER JOIN keyword_question ON keyword.keywordid =
keyword_question.keywordid

The link child and master key fields are questionid.

So what I am trying to maintain in the form is a many to many relationship
bewteen Question and Keyword tables.

When I try to add a keyword to a question, I make a selection in a cbo
with the keywordid as the control source and then I type some text in the
note textbox.

As soon as I move off the textbox to the next record I get a dialog box
that states: "key value for this row was changed or deleted at the data
store. The local row is now deleted."

I click OK to close the dialog box but now when I try to move to any field
on the form I get anoither dialog box that says "Multi-step operation
generated errors. Check each status value."

I must close the form which then triggers the following error "Another
user or application has deleted this record or changed the value of the
primary key."

I then get repeated "Multi-step operation generated errors. Check each
status value." messages until I can finally reopen the form where I see
that the record I original tried to create was in fact actually created.

What could be causing this?

I am simply trying to INSERT a record into the question_keyword table (see
table def below).

There are no triggers on any of the tables involved (Question,
Question_keyword, and keyword). And there are no cascades on the FKs.

Does anyone have any idea? It worked fine before the upsizing.

Thanks
Dave


CREATE TABLE [dbo].[keyword_question] (
[keywordquestionid] [int] IDENTITY (1, 1) NOT NULL ,
[keywordid] [int] NULL ,
[questionid] [int] NULL ,
[note] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createdate] [datetime] NULL
) ON [PRIMARY]
GO

 

[Dave]

Sylvia:

Thanks much. That appears it will do the trick.

Just one dumb question: how are you entering the stored proc name into the
form's property sheet?

I have tried « MyResyncSP ? » , <<MyResyncSP ?>> , "MyResyncSP ?" ,
'MyResyncSP ?' and a host of others but I either get "incoreccted syntax
after <" or "stored proc cannot be found."

Thanks

 

[Sylvain Lafontaine]

Just drop the quotes:

MyResyncSP ?

Of course, use the proper name for the stored procedure. Take a look with
the SQL-Profiler to make sure that it's called by ADP.

 

[Dave]

Thanks Sylvain but I am still struggling with this.

Using this as the value for the resynch command...
<<adpAccessResynch_Keyword_Question ?>>.

... I get a "incorecct syntax " message but other than that I get no further
errors and the record is saved.

In profiler I get this under RPC: Completed...
exec sp_execute 13, 18, 1, 'test'
exec sp_executesql N'<<adpAccessResynch_Keyword_Question @P1>>', N'@P1
int', 0

Using the name of the stored proc without any quotes or brackets as you
suggested (e.g., adpAccessResynch_Keyword_Question ?), I get "Key value
for this row was changed or deleted at the data store. The local row is now
deleted." I then get a succession of errors and have to closed the form
before Access calms down and I can continue. When I open the form back up I
find the record has been saved.

In profiler I get this under RPC: Completed...
exec sp_execute 20, 18, 1, 'test2'
exec sp_executesql N'exec adpAccessResynch_Keyword_Question @P1', N'@P1
int', 0

The recordsource for the subform is:
SELECT keyword.keyword, keyword_question.keywordid,
keyword_question.questionid, keyword_question.note FROM keyword INNER JOIN
keyword_question ON keyword.keywordid = keyword_question.keywordid

Unique Table = keyword_question
Recordset Type = Updatable Snapshot

The script for the stored proc looks like this.

IF object_id('adpAccessResynch_Keyword_Question') IS NOT NULL
DROP PROCEDURE dbo.adpAccessResynch_Keyword_Question
GO

/*
Title: adpAccessResynch_Keyword_Question
Creator:

Purpose: Resynchs the Access subform
Created: 9/16/07

Comments:

Example:
EXEC adpAccessResynch_Keyword_Question @keywordquestionid=22

Output:
keyword varchar
, keyword_question.keywordid int
, keyword_question.questionid int
, keyword_question.note varchar

MODIFICATIONS:
Date Developer Comment
---------- --------- -------------------------------------------------------------------

*/


CREATE PROCEDURE dbo.adpAccessResynch_Keyword_Question
@keywordquestionid int
AS
SET NOCOUNT ON

BEGIN

SELECT keyword.keyword
, keyword_question.keywordid
, keyword_question.questionid
, keyword_question.note
FROM keyword
INNER JOIN keyword_question ON keyword.keywordid =
keyword_question.keywordid
WHERE keyword_question.keywordquestionid = @keywordquestionid

RETURN 0

END
GO
GRANT EXECUTE ON dbo.adpAccessResynch_Keyword_Question TO xx

 

[Sylvain Lafontaine]

Setting the UniqueTable might be sufficient in your case but with more
complicated case, it's better to also set the ResyncCommand. Your second
version, the one without the «» or the << >>, is the correct one.

From you description of the keyword_question table, I'm not sure if you have
defined the primary key for it. Also, if you have other unique indexes on
this table, it's possible that there is confusion between one of these
unique indexe and the primary key; especially if their names don't come
after the name of the primary key alphabetically. (This is an old problem
that I think have been corrected a long time ago but I'm not sure with ADP.)

As you can see from the lines « exec sp_execute 13, 18, 1, 'test' » and «
exec sp_execute 20, 18, 1, 'test2' »; your records (or keywords) have been
corrected inserted: the numbers 13 and 20 refer to prepared stored
procedures and 18, 1, 'test' are the parameters for the insertion:
keywordid, questionid and note.

 

[Charles Wang[MSFT]]

Hi Dave,
Regarding your questions, I agreed to Sylvain's responses, so I did not
provide answer before.
Now I would like to check with you the issue status. If you have any
questions or concerns, please feel free to post back. We are glad to work
with you for further research.

Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================