1 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

2 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

3 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

Make that locations :rolleyes:

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

4 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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

Gordon Barker
Biological Survey Data Manager
National Trust

5 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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)?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

6 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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

John van Breda
Biodiverse IT

johnvanbreda's Website

7 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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

Gordon Barker
Biological Survey Data Manager
National Trust

8 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

9 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: The INSERT statement conflicted with the FOREIGN KEY constraint

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.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687