Sign in Register
Go to solution
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Jesper Ordrup 1019 posts 1528 karma points MVP
    Dec 03, 2014 @ 13:04
    Jesper Ordrup
    1

    Contstraint erros when scripting to upload an umbraco database

    Hi all,

    I've always refrained from using this method to copy/move a database because of the constraint errors. What do you do? Ignore and continue? Or has anyone nailed the order the tables should be scripted in?

    best
    Jesper

     

    Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsContentTypeAllowedContentType_cmsContentType'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsContentTypeAllowedContentType_cmsContentType' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsContentTypeAllowedContentType_cmsContentType1'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsContentTypeAllowedContentType_cmsContentType1' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContent' that match the referencing column list in the foreign key 'FK_cmsContentVersion_cmsContent_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsContentVersion_cmsContent_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContent' that match the referencing column list in the foreign key 'FK_cmsContentXml_cmsContent_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsContentXml_cmsContent_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsDataType' that match the referencing column list in the foreign key 'FK_cmsDataTypePreValues_cmsDataType_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsDataTypePreValues_cmsDataType_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContent' that match the referencing column list in the foreign key 'FK_cmsDocument_cmsContent_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsDocument_cmsContent_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsTemplate' that match the referencing column list in the foreign key 'FK_cmsDocument_cmsTemplate_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsDocument_cmsTemplate_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsDocumentType_cmsContentType_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsDocumentType_cmsContentType_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsTemplate' that match the referencing column list in the foreign key 'FK_cmsDocumentType_cmsTemplate_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsDocumentType_cmsTemplate_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsDictionary' that match the referencing column list in the foreign key 'FK_cmsLanguageText_cmsDictionary_id'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsLanguageText_cmsDictionary_id' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContent' that match the referencing column list in the foreign key 'FK_cmsMember_cmsContent_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsMember_cmsContent_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsMemberType_cmsContentType_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsMemberType_cmsContentType_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContent' that match the referencing column list in the foreign key 'FK_cmsPreviewXml_cmsContent_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsPreviewXml_cmsContent_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContentVersion' that match the referencing column list in the foreign key 'FK_cmsPreviewXml_cmsContentVersion_VersionId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsPreviewXml_cmsContentVersion_VersionId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsPropertyType_cmsContentType_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsPropertyType_cmsContentType_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsDataType' that match the referencing column list in the foreign key 'FK_cmsPropertyType_cmsDataType_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsPropertyType_cmsDataType_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsPropertyTypeGroup_cmsContentType_nodeId'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsPropertyTypeGroup_cmsContentType_nodeId' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.cmsContent' that match the referencing column list in the foreign key 'FK_cmsTagRelationship_cmsContent'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_cmsTagRelationship_cmsContent' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
    Copy Link
  • Jan Skovgaard 11280 posts 23678 karma points MVP 11x admin c-trib
    Dec 03, 2014 @ 13:06
    Jan Skovgaard
    0

    Hi Jesper

    Are the database versions the same? Or are you trying to go from MSSQL 2012 to 2008 R2 for instance?

    /Jan

    Copy Link
  • Jesper Ordrup 1019 posts 1528 karma points MVP
    Dec 03, 2014 @ 13:12
    Jesper Ordrup
    0

    Hello Jan!

    Nope its 2012 to 2012 .. 

    /j

    Copy Link
  • Jesper Ordrup 1019 posts 1528 karma points MVP
    Dec 04, 2014 @ 09:39
    Jesper Ordrup
    0

    Hi all,

    Am I the only one getting these constrain errors?

    best
    Jesper 

    Copy Link
  • JorgeV 2 posts 22 karma points
    Feb 20, 2015 @ 17:29
    JorgeV
    0

    Nop, it happens to me also going from 2014 to 2014.

     

    (1 row(s) affected)

    Msg 1776, Level 16, State 0, Line 35570

    There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsContent_cmsContentType_nodeId'.

    Copy Link
  • Juho Vuotila 3 posts 23 karma points
    Mar 03, 2015 @ 15:05
    Juho Vuotila
    0

    Same problem here. And the problem is trying to create reference from int column to another int column. Int is not unique field, so how this reference can ever work? There can be same value multiple times and where shoud that foreign key reference point then?

    It seems that Umbraco generates unstandard database which cannot be restored identically by scripting it. I think this is big design bug. There is punch of these kind corrupted references as we can see on the first post.

    Copy Link
  • Tom 713 posts 954 karma points
    May 27, 2016 @ 06:19
    Tom
    0

    Hi All, Has there been any resolution on this?

    We're using 7.4.1 and this is still an issue. We can't script out the db in 2012 without these constraint errors.

    Copy Link
  • Rahul Sekhar 9 posts 85 karma points
    Oct 26, 2016 @ 19:26
    Rahul Sekhar
    0

    Same problem on Umbraco 7.5.3. Any updates on this?

    Copy Link
  • Manos Gatsios 21 posts 41 karma points
    Feb 15, 2017 @ 10:14
    Manos Gatsios
    0

    Hi there,

    Any idea (yet) on how to overcome the problem described in this post?

    We are now using version 7.5.8 and still constraint errors making the generated scripts unusable! I'd really appreciated if somebody could shed some light on this.

    Thanks in advance

    Manos

    Copy Link
  • Dave Woestenborghs 3504 posts 12135 karma points MVP 9x admin c-trib
    Feb 15, 2017 @ 10:57
    Dave Woestenborghs
    0

    Hi Jesper,

    Best is to disable the constraints when inserting data using script. And enable them again when ready.

    -- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL

    Dave

    Copy Link
  • Manos Gatsios 21 posts 41 karma points
    Feb 15, 2017 @ 11:32
    Manos Gatsios
    0

    Hi Dave,

    Thanks for your advice but the following error:

    Msg 1776, Level 16, State 0, Line 1498
There are no primary or candidate keys in the referenced table 'dbo.cmsContentType' that match the referencing column list in the foreign key 'FK_cmsContent_cmsContentType_nodeId'.

    is the result of running the following statement:

    IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_cmsContent_cmsContentType_nodeId]') AND parent_object_id = OBJECT_ID(N'[dbo].[cmsContent]'))
   ALTER TABLE [dbo].[cmsContent]  WITH CHECK ADD  CONSTRAINT [FK_cmsContent_cmsContentType_nodeId] FOREIGN KEY([contentType])
   REFERENCES [dbo].[cmsContentType] ([nodeId])
GO

    So I don't see how it would solve the problem by just disabling the constraints on the table.

    I am not inserting data, I merely creating an empty database!

    Manos

    Copy Link
  • Dave Woestenborghs 3504 posts 12135 karma points MVP 9x admin c-trib
    Feb 15, 2017 @ 11:46
    Dave Woestenborghs
    0

    Hi Manos,

    If you don't need to insert data you don't need to turn off the constraints.

    I think in your case the order the tables are created is wrong. It tries to create a FK on a table that not exists yet.

    But why would you just create a empty database. This handled by the Umbraco installer.

    Dave

    Copy Link
  • Manos Gatsios 21 posts 41 karma points
    Feb 15, 2017 @ 13:21
    Manos Gatsios
    0

    Hi again Dave,

    We are in the development of a project which we need to currently test install at the customer's premises and to which unfortunately have no access to it from outside (security policies).

    So the strict requirement is to send only scripts over (schema & data) to the IT guy to recreate the database for us. We are not even allowed to send a backup of our DB over!

    Of course we are also sending a ZIP file containing the developed site, meaning there is not such an option of running the Umbraco installer.

    The scripts are generated in SSMS and if the order of creation is wrong (which I doubt because I've checked it) then it's a mistake of the tool itself! From what I can see the table is created without problems and is available but the statement to add the new constraint is failing!

    Manos

    Copy Link
  • Anthony 28 posts 147 karma points
    Jun 22, 2017 @ 22:19
    Anthony
    6

    Just hit this issue wit Umbraco 7.3. I was exporting an SQL script of the database with both Schema and Data. The problem was I didn't ask MSSQL 2017 to export the Unique indexes so the constraints fail when you try to apply them - as they have to be applied to Primary Keys or Unique Indexes.

    The solution is to select script indexes when creating the export.

    So to export using MSSQL:

    • Click database.
    • Tasks>Generate Scripts>Select Tables>Click Advanced.
    • Select to export Schema and\or data as required.
    • Under Table view options > script full text indexes(may not be necessary)
    • Under Table view options > script indexes
    • Finish

    This will then import the constraints. Hopefully you can fill in the blanks. Good luck.

    Copy Link
  • Kieron McIntyre 117 posts 360 karma points
    Jul 07, 2017 @ 13:15
    Kieron McIntyre
    100

    Hi @Anthony,

    Setting "Script indexes" to true worked for me just now. Thanks for helping out.

    Copy Link
  • Jesper Ordrup 1019 posts 1528 karma points MVP
    Jan 10, 2022 @ 09:36
    Jesper Ordrup
    0

    Never too late to mark somthing a solution.

    Thanks :-)

    Copy Link
  • Jesper Ordrup 1019 posts 1528 karma points MVP
    Jan 10, 2022 @ 09:38
    Jesper Ordrup
    0

    I accidently marked the wrong answer as the solution. This is the correct.

    Under Table view options > script indexes
    Copy Link
  • Jakob Lithner 61 posts 264 karma points
    Oct 01, 2018 @ 16:46
    Jakob Lithner
    0

    You saved my day!

    Copy Link
  • Amir Khan 1287 posts 2744 karma points
    Feb 25, 2019 @ 19:21
    Amir Khan
    0

    Life saver right here.

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft