Insert... Conflicted with foreign key constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Insert... Conflicted with foreign key constraint

Author

Topic

bob123
Starting Member

16 Posts

Posted - 2010-03-11 : 16:06:25

Hi, any help with this appreciated
I have 2 tables QuestionAnswer and SurveyAnswer when I try and import data for the QuestionAnswer table I receive the following error msg...

"The INSERT statement conflicted with the FOREIGN KEY constraint "fk_QuestionAnswer_QuestionSection". The conflict occurred in database "ExitSurvey", table "exitsurvey.QuestionSection", column SectionID'.".
(SQL Server Import and Export Wizard)

CREATE TABLE [XXXX].[QuestionAnswer](
[AnswerID] [int] NOT NULL,
[QuestionID] [int] NOT NULL,
[SectionID] [int] NULL,
[Answer] [varchar](255) NOT NULL,
[Type] [char](1) NOT NULL,
[Order] [smallint] NULL
)
ALTER TABLE XXXX.QuestionAnswer
ADD CONSTRAINT pk_QuestionAnswer PRIMARY KEY (AnswerID)

ALTER TABLE XXXX.QuestionAnswer
ADD CONSTRAINT fk_QuestionAnswer_QuestionSection
FOREIGN KEY (SectionID)
REFERENCES XXXX.QuestionSection (SectionID)

No data in QuestionAnswer table

=================================================================
CREATE TABLE [XXXX].[QuestionSection](
[SectionID] [int] NOT NULL,
[QuestionID] [int] NOT NULL,
[SectionHeader] [varchar](255) NOT NULL,
[Order] [smallint] NULL
)

ALTER TABLE XXXX.QuestionSection
ADD CONSTRAINT pk_QuestionSection PRIMARY KEY (SectionID)
GO

ALTER TABLE XXXX.QuestionSection
ADD CONSTRAINT fk_QuestionSection_Question
FOREIGN KEY (QuestionID)
REFERENCES exitsurvey.Question (QuestionID)
==================================================================
Data in questionsection table

1 5 Work conditions or environment 1
2 5 Work relations 2
3 5 Professional Development 3
4 5 Agreement Terminated 4
5 5 Personal/Family/Whanau Reasons 5
6 6 Another NZ Health Provider 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-11 : 17:51:22

Seems like you're trying to insert a SectionID into QuestionAnswer that isn't existent in table QuestionSection.

No, you're never too old to Yak'n'Roll if you're too young to die.

bob123
Starting Member

16 Posts

Posted - 2010-03-11 : 18:58:03

Thanks for the reply, I have 2 text files that are supposed to populate these tables I imported the questionsection data without a problem.

the questionanswer data is .....

AnswerID|QuestionID|SectionID|Answer|Type|Order
1|1|-1|0-2 yrs|C|1
2|1|-1|2-5 yrs|C|2
3|1|-1|5-10 yrs|C|3
4|1|-1|10 yrs+|C|4
5|1|-1|Other|T|5
6|2|-1|Medical|C|1
7|2|-1|Public Health|C|2
8|2|-1|Mental Health|C|3
9|2|-1|Corporate|C|4
10|2|-1|Surgical|C|5
11|2|-1|AT&R & Community|C|6
12|2|-1|Clinical Support|C|7
13|2|-1|Funding Division|C|8
14|2|-1|Child Health|C|9
15|2|-1|RCTS|C|10
16|2|-1|Enable |C|11
17|2|-1|Women's Health|C|12
18|2|-1|Child & Adolescent Oral Health|C|13
19|2|-1|Breast Screening|C|14
20|2|-1|M Health|C|15
21|2|-1|Other|T|16
22|3|-1| North Hospital|C|1
23|3|-1|PN Community|C|2
24|3|-1|H|C|3
25|3|-1|Clevely|C|4
26|3|-1|D|C|5
27|3|-1|Community|C|6
28|3|-1|Other|T|7
29|4|-1|Administration|C|1
30|4|-1|Nursing|C|2
31|4|-1|Allied Health|C|3
32|4|-1|Non-Clinical Support|C|4
33|4|-1|Clinical Support|C|5
34|4|-1|Technical|C|6
35|4|-1|Medical|C|7
36|4|-1|Other |T|8
37|5|1|Physical environment|D|1
38|5|1|Resources or equipment|D|2
39|5|1|Terms and conditions of employment|D|3
40|5|2|Interpersonal relationships|D|1
41|5|2|Team dynamics|D|2
42|5|2|Lack of job satisfaction|D|3
43|5|2|Lack of cultural support/supervision|D|4
44|5|3|Change in profession/different role|D|1
45|5|3|Career development opportunities/advancement|D|2
46|5|3|Study or training|D|3
47|5|4|End of fixed term agreement|D|1
48|5|4|Restructuring/redundancy|D|2
49|5|4|Dismissed|D|3
50|5|5|Health related|D|1
51|5|5|Overseas travel|D|2
52|5|5|Domestic reasons|D|3
53|5|5|Not returning from parental leave/LWOP|D|4
54|5|5|Non-work or work accident|D|5
55|5|5|Retirement|D|6
56|5|5|Relocating with partner/family|D|7
57|5|-1|Other|T|1
58|6|-1|Another DHB|C|1
59|6|-1|Non Health Sector|C|2
60|6|-1|Overseas health job|C|3
61|6|-1|Ceasing work|C|4
62|6|6|Another Health Provider - Local|C|1
63|6|6|Another Health Provider - Elsewhere|C|2
64|6|6|Another Health Provider - NGO|C|3
65|6|-1|Overseas non-health job|C|5
66|6|-1|Another position within the M region|C|6
67|6|-1|Other|T|7
68|7|-1|Physical environment|C|1
69|7|-1|Working relationships|C|2
70|7|-1|Equipment/resources|C|3
71|7|-1|Acknowlegement/recognition|C|4
72|7|-1|Support/professional supervision|C|5
73|7|-1|Communication|C|6
74|7|-1|Job design|C|7
75|7|-1|Career/professional development opportunities|C|8
76|7|-1|Team structure|C|9
77|7|-1|Cultural support/supervision|C|10
78|7|-1|Work flexibility|C|11
79|7|-1|Regular performance appraisals/feedback|C|12
80|7|-1|Not applicable|C|13
81|7|-1|Other|T|14
82|8|-1|Professional development|C|1
83|8|-1|My team|C|2
84|8|-1|Flexibility e.g. hours|C|3
85|8|-1|Achievement/challenge|C|4
86|8|-1|Recognition/support|C|5
87|8|-1|Work environment|C|6
88|8|-1|Social opportunities|C|7
89|8|-1|Other|T|8
90|9|-1|Yes|C|1
91|9|-1|No|T|2

It would seem that the -1 sectionid which is not in the questionsection table is the problem.

But I can't see how this can be resolved as I would have to enter the -1 sectionid for very question into the questionsection table which would be entering duplicate values for the primary key.

Any suggestions?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 01:20:07

duplicate values for the primary key?
You need only one entry in the questionsection table with the value -1.
What is the problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources