# Error during database upgrade
**URL:** https://forum.litium.com/t/error-during-database-upgrade/506
**Category:** Questions
**Tags:** litium-7
**Created:** [January 17, 2019, 12:33pm UTC](https://forum.litium.com/t/error-during-database-upgrade/506 "2019-01-17T12:33:55Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 17, 2019, 12:33pm UTC](https://forum.litium.com/t/error-during-database-upgrade/506/1 "2019-01-17T12:33:55Z")
When trying to upgrade the database from 6.2.3 to 7.0.2 the error below occurs. What causes it to and what do we need to do to fix it so we can complete the upgrade?
```
Error in SQL
Exception calling "ExecuteNonQuery" with "0" argument(s): "The INSERT statement conflicted with the
FOREIGN KEY constraint "FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId".
The conflict occurred in database " *****", table "FieldFramework.FieldDefinition", column 'SystemId'.
The statement has been terminated."
```
Litium version: 7
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 17, 2019, 1:44pm UTC](https://forum.litium.com/t/error-during-database-upgrade/506/2 "2019-01-17T13:44:05Z")
Regarding to the error message the `FieldDefinitionFieldData` table have rows but missing the owner in the `FieldDefinition` table, the tables exists for each area.
How you have ended up in this situation is unknown, but you should be able to clean out the `FieldDefinitionFieldData` tables from rows that missing the owner in `FieldDefinition`.
---
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 18, 2019, 8:43am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/3 "2019-01-18T08:43:49Z")
This query returns four rows; do you suggest we delete these rows?
```
SELECT * FROM Products.FieldDefinition
WHERE SystemId NOT IN (
SELECT OwnerSystemId
FROM Products.FieldDefinitionFieldData
)
```
```
SystemId CanBeGridColumn CanBeGridFilter FieldType Id MultiCulture
------------------------------------ --------------- --------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------
FDC75807-B0E5-4359-90AC-501EA3566992 1 0 Text TempArticleStructureValues 0
9AA7EE90-88CA-4F5B-8A56-6C00CB0B25EB 0 0 MultirowText TagSorting 0
B64125BC-B00D-427E-BBA9-71424690528B 1 0 Decimal BrandId 0
16DF6CAD-569D-4778-8EAE-90015D5E0EC3 1 0 MediaPointerImage BrandImage 0
(4 rows affected)
```
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 18, 2019, 9:07am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/4 "2019-01-18T09:07:57Z")
Its the table `*.FieldDefinitionFieldData` that have the rows without the parent so you need to switch place on the table in the sql to something like
```auto
SELECT * FROM Products.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
SELECT SystemId
FROM Products.FieldDefinition
)
```
---
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 18, 2019, 9:34am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/5 "2019-01-18T09:34:39Z")
I thought so at first but those queries returns no data:
```
SELECT * FROM Customers.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
SELECT SystemId
FROM Customers.FieldDefinition
)
SELECT * FROM Media.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
SELECT SystemId
FROM Media.FieldDefinition
)
SELECT * FROM Products.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
SELECT SystemId
FROM Products.FieldDefinition
)
```
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 18, 2019, 9:59am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/6 "2019-01-18T09:59:25Z")
The foregin key is the following
```auto
ALTER TABLE [FieldFramework].[FieldDefinitionFieldData] WITH CHECK ADD CONSTRAINT [FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId] FOREIGN KEY([OwnerSystemId])
REFERENCES [FieldFramework].[FieldDefinition] ([SystemId])
ON DELETE CASCADE
```
so it should be if you try to inserting a row in that table that not have a matching system id.
I think you need to execute the sql-script manual and see what part of the script is creating this failure.
---
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 18, 2019, 10:19am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/7 "2019-01-18T10:19:42Z")
Yep, I just ran the script manually and got the same error:
```
Msg 547, Level 16, State 0, Line 24012
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId". The conflict occurred in database "nwt", table "FieldFramework.FieldDefinition", column 'SystemId'.
The statement has been terminated.
```
And these are the lines in the script (line 24007 and forward):
```
-- Set OptionField types
INSERT INTO [FieldFramework].[FieldDefinitionFieldData] ([OwnerSystemId],[FieldDefinitionId],[Culture],[Index],[JsonValue])
SELECT
CPP.SystemId as OwnerSystemId,
''_option'' as [FieldDefinitionId],
''*'' as [Culture],
0 as [Index],
dbo.GetPageOptionFieldType(CPP.PropertyName,CPP.PropertyType,CPP.IsArray) as [JsonValue]
FROM @pageProperties AS CPP
INNER JOIN [FieldFramework].[FieldDefinition] FD ON FD.SystemId = CPP.SystemId
WHERE FD.FieldType = ''Pointer'' OR FD.FieldType = ''MultiField'';
```
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 18, 2019, 10:38am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/8 "2019-01-18T10:38:04Z")
Did you have any output from the pre-req database script?
---
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 18, 2019, 12:30pm UTC](https://forum.litium.com/t/error-during-database-upgrade/506/9 "2019-01-18T12:30:47Z")
I thought not but had a look again and yes, there were some errors (see below).
```
|Description|Type|
|---|---|
|Could not convert Content Property "ButtonText" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "ButtonUrl" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CampaignBottomParagraph" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CampaignShortDescription" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Canonical" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CssClass" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "customCss" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CustomCssForHeader" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "customHtml" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Description" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "EmailSender" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Files" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Heading" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Image1" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Image2" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Introduction" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Link" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "SubHeading" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Terms" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Text" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
```
---
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 21, 2019, 10:26am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/10 "2019-01-21T10:26:41Z")
We’ve solved the errors reported by the pre-req script but still get this error from the upgrade script.
Any ideas?
```
Msg 547, Level 16, State 0, Line 24012
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId". The conflict occurred in database "nwt", table "FieldFramework.FieldDefinition", column 'SystemId'.
The statement has been terminated.
```
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 21, 2019, 11:08am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/11 "2019-01-21T11:08:17Z")
Please, open a support ticket where you can provide a database backup so we can help out to find what is causing the upgrade error.
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 21, 2019, 1:44pm UTC](https://forum.litium.com/t/error-during-database-upgrade/506/13 "2019-01-21T13:44:28Z")
I got the ticket and will come back after I analyzed the error.
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 21, 2019, 8:23pm UTC](https://forum.litium.com/t/error-during-database-upgrade/506/14 "2019-01-21T20:23:50Z")
> [@hans](#):
>
> We’ve solved the errors reported by the pre-req script but still get this error from the upgrade script.
Do you have a database script to update to solve the errors? The database that you provided have still errors for them.
---
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 21, 2019, 9:29pm UTC](https://forum.litium.com/t/error-during-database-upgrade/506/15 "2019-01-21T21:29:04Z")
Try this (untested but at least got rid of the errors):
```
--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.*
UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
from CMS_PageTypeProperty prop
Inner join CMS_PageType On CMS_PageType.PageTypeID = prop.PageTypeID
Where PropertyName in(
SELECT a.PropertyName
FROM (SELECT PropertyName
FROM dbo.CMS_PageTypeProperty
GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
GROUP BY PropertyName
HAVING COUNT(1) > 1)
Go
--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.*
UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
from CMS_PageProperty prop
Inner join CMS_PageType On CMS_PageType.PageTypeID = prop.PageTypeID
Where PropertyName in(
SELECT a.PropertyName
FROM (SELECT PropertyName
FROM dbo.CMS_PageTypeProperty
GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
GROUP BY PropertyName
HAVING COUNT(1) > 1)
Go
UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.*
from CMS_PagePropertyValue prop
Inner join CMS_PageType On CMS_PageType.PageTypeID = prop.PageTypeID
Where PropertyName in(
SELECT a.PropertyName
FROM (SELECT PropertyName
FROM dbo.CMS_PageTypeProperty
GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
GROUP BY PropertyName
HAVING COUNT(1) > 1)
Go
UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.*
from [CMS_VersionProperty] prop
Inner join CMS_Page On CMS_Page.PageID = prop.VersionID
Inner join CMS_PageType On CMS_PageType.PageTypeID = CMS_Page.PageTypeID
Where PropertyName in(
SELECT a.PropertyName
FROM (SELECT PropertyName
FROM dbo.CMS_PageTypeProperty
GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
GROUP BY PropertyName
HAVING COUNT(1) > 1)
Go
--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.*
UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
from CMS_WorkingCopyProperty prop
Inner join CMS_Page On CMS_Page.PageID = prop.WorkingCopyID
Inner join CMS_PageType On CMS_PageType.PageTypeID = CMS_Page.PageTypeID
Where PropertyName in(
SELECT a.PropertyName
FROM (SELECT PropertyName
FROM dbo.CMS_PageTypeProperty
GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
GROUP BY PropertyName
HAVING COUNT(1) > 1)
Go
```
We also had to change some of the VAT percentages to get past the pre-req script…
```
Update Products.PriceListItem Set VatPercentage = 0.250000
from Products.PriceListItem pli
Inner join Products.PriceList pl On pl.SystemId = pli.PriceListSystemId
INNER JOIN Foundation_Currency c on c.CurrencyID = pl.CurrencySystemId
Where VatPercentage <= 1 And VatPercentage >= -1 And CurrencyCode <> 'EUR'
Go
Update Products.PriceListItem Set VatPercentage = 0.240000
from Products.PriceListItem pli
Inner join Products.PriceList pl On pl.SystemId = pli.PriceListSystemId
INNER JOIN Foundation_Currency c on c.CurrencyID = pl.CurrencySystemId
Where VatPercentage <= 1 And VatPercentage >= -1 And CurrencyCode = 'EUR'
Go
```
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 22, 2019, 7:54am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/16 "2019-01-22T07:54:29Z")
Your script for replacing property name was not including all needed tables. I put this script together and now I can run the upgrade script without errors.
```auto
UPDATE PLI SET PLI.VatPercentage = 0.250000
FROM Products.PriceListItem PLI
INNER JOIN Products.PriceList PL ON PL.SystemId = PLI.PriceListSystemId
INNER JOIN Foundation_Currency C ON C.CurrencyID = PL.CurrencySystemId
WHERE VatPercentage <= 1 AND VatPercentage >= -1 AND CurrencyCode <> 'EUR'
GO
UPDATE PLI Set VatPercentage = 0.240000
FROM Products.PriceListItem PLI
INNER JOIN Products.PriceList PL ON PL.SystemId = PLI.PriceListSystemId
INNER JOIN Foundation_Currency C ON C.CurrencyID = PL.CurrencySystemId
WHERE VatPercentage <= 1 AND VatPercentage >= -1 AND CurrencyCode = 'EUR'
GO
UPDATE PTP Set PTP.PropertyName = PT.PagetypeName + '-' + PTP.PropertyName
FROM dbo.CMS_PageTypeProperty PTP
INNER JOIN dbo.CMS_PageType PT On PT.PageTypeID = PTP.PageTypeID
WHERE PTP.PropertyName IN (
SELECT a.PropertyName
FROM (SELECT PropertyName
FROM dbo.CMS_PageTypeProperty
GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
GROUP BY a.PropertyName
HAVING COUNT(1) > 1
)
GO
UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_PageProperty PP ON PP.PageID = P.PageID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO
UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_PagePropertyValue PP ON PP.PageID = P.PageID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO
UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_Version V ON P.PageID = V.PageID
INNER JOIN dbo.CMS_VersionProperty PP ON PP.VersionID = V.VersionID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO
UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_WorkingCopy W ON P.PageID = W.PageID
INNER JOIN dbo.CMS_WorkingCopyProperty PP ON PP.WorkingCopyID = W.WorkingCopyID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO
```
---
### Author:  [@patric.forsgard](https://forum.litium.com/u/patric.forsgard)
#### Post date: [January 23, 2019, 9:53am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/17 "2019-01-23T09:53:46Z")
@hans Did my updated script for you solve the upgrade problem?
---
### Author:  [@hans](https://forum.litium.com/u/hans)
#### Post date: [January 23, 2019, 11:34am UTC](https://forum.litium.com/t/error-during-database-upgrade/506/18 "2019-01-23T11:34:09Z")
Yes, thank you!