NBN Forum / The INSERT statement conflicted with the FOREIGN KEY constraint

charliebarnes · 2010-03-12 09:14:16

Importing from one Recorder 6 database to another....

Failed to insert JNCC0045000007A9 into TAXON_OCCURRENCE
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_OCCURRENCE_SAMPLE". The conflict occurred in database "R6_LERC1", table "dbo.SAMPLE", column 'SAMPLE_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
--------------------

And hundreds more.

charliebarnes · 2010-03-15 10:08:18

On further digging it appears Recorder forgot to export a location, causing a cascade of failures.

Any ideas why it didn't want to export this particular location?

charliebarnes · 2010-03-15 11:07:15

Make that locations

Gordon Barker · 2010-03-15 13:23:39

Don't know if it will be the same issue, but, when transferring a large-ish number of records to a new set up database on a laptop for demonstration/training purposes, I had to do 2 export/imports, the first with the locations and the second with the records. Don't know why, but it seemed to work.

Gordon

charliebarnes · 2010-03-15 14:15:08

Thanks Gordon, I'll give that a go.

Just tried exporting another (much smaller) survey from the same database and it contains no taxon occurences (i.e. empty samples)?

johnvanbreda · 2010-03-15 19:46:38

Would either of you be able to provide more details on how to reproduce this?

Gordon Barker · 2010-03-16 09:11:35

Sorry John, was about 18 months ago and I can't remember the details.

charliebarnes · 2010-03-16 09:26:00

johnvanbreda wrote:
Would either of you be able to provide more details on how to reproduce this?

All I'm trying to do is an export of a survery from one database to another - I can only assume that there is something that Recorder doesn't like about these particular locations and taxon occurences.

sallyrankin · 2010-03-17 08:07:30

What version of Recorder 6 are you using? Are you suffering from the following problem in http://forums.nbn.org.uk/viewtopic.php?pid=3069#p3069: 'Another problem in v6.10 and v6.13 is that the parents of the parents of locations may not be included in the export file. The locations for all the survey events and samples are included for the selected observations, and their parents, but not the parents of the parents unless other observations bring them in. To resolve this problem you need to identify the missing locations in the export file, i.e. find Parent_keys in the Location table that don’t have their own entry in the Location table, and import these before importing the other file. If you put these in a rucksack you will be able to use it to export the required locations from the system that the other export came from.'
According to the release notes this was fixed in v6.14.4.212.

Announcement

#1 2010-03-12 09:14:16

The INSERT statement conflicted with the FOREIGN KEY constraint

#2 2010-03-15 10:08:18

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

#3 2010-03-15 11:07:15

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

#4 2010-03-15 13:23:39

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

#5 2010-03-15 14:15:08

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

#6 2010-03-15 19:46:38

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

#7 2010-03-16 09:11:35

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

#8 2010-03-16 09:26:00

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

johnvanbreda wrote:

#9 2010-03-17 08:07:30

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

Board footer