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 957 posts 1321 karma points MVP
    Dec 03, 2014 @ 13:04
    Jesper Ordrup
    0

    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.
    
  • Jan Skovgaard 10936 posts 21802 karma points MVP 4x 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

  • Jesper Ordrup 957 posts 1321 karma points MVP
    Dec 03, 2014 @ 13:12
    Jesper Ordrup
    0

    Hello Jan!

    Nope its 2012 to 2012 .. 

    /j

  • Jesper Ordrup 957 posts 1321 karma points MVP
    Dec 04, 2014 @ 09:39
    Jesper Ordrup
    0

    Hi all,

    Am I the only one getting these constrain errors?

    best
    Jesper 

  • 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'.

  • 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.

  • Tom 679 posts 895 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.

  • Rahul Sekhar 9 posts 83 karma points
    Oct 26, 2016 @ 19:26
    Rahul Sekhar
    0

    Same problem on Umbraco 7.5.3. Any updates on this?

  • Manos Gatsios 20 posts 40 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

  • Dave Woestenborghs 2798 posts 8563 karma points MVP 3x 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

  • Manos Gatsios 20 posts 40 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

  • Dave Woestenborghs 2798 posts 8563 karma points MVP 3x 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

  • Manos Gatsios 20 posts 40 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

  • Anthony 19 posts 132 karma points
    Jun 22, 2017 @ 22:19
    Anthony
    0

    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.

  • Kieron McIntyre 96 posts 312 karma points
    Jul 07, 2017 @ 13:15
    Kieron McIntyre
    0

    Hi @Anthony,

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

Please Sign in or register to post replies

Write your reply to:

Draft