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


  • Greg Berlin 818 posts 634 karma points
    Jan 21, 2013 @ 02:52
    Greg Berlin
    0

    Database Constraints issue..

    I had a weird problem today where i deleted a property from a document type, but the DB didn't automatically delete it.  So i got some constraint errors.

    Looking in the DB, i found that cmsPropertyType table still had the value, but it was not in the cmsPropertyData table (or the link to the doc type)... yet when i tried in Umbraco to delete the property type, it said that it was violating a foreign key constraint.  As deeply as i looked i couldn't find how this was the case, so i deleted the constraint and got rid of the dodgy field.

    All is okay now, but i can't put the constraint back.  THe constraint i deleted was this one:

    FK_cmsPropertyData_cmsPropertyType

    I scripted the Create statement for the constraint (as follows)

    ALTER TABLE [dbo].[cmsPropertyData]  WITH CHECK ADD  CONSTRAINT [FK_cmsPropertyData_cmsPropertyType] FOREIGN KEY([propertytypeid])
REFERENCES [dbo].[cmsPropertyType] ([id]
    GO
    ALTER TABLE [dbo].[cmsPropertyData] CHECK CONSTRAINT [FK_cmsPropertyData_cmsPropertyType]
GO

    But when i run it, i get the following error:

    Msg 547, Level 16, State 0, Line 1
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_cmsPropertyData_cmsPropertyType". The conflict occurred in database "umbraco_pps", table "dbo.cmsPropertyType", column 'id'.

    Can somebody help me get that constraint back in there please?

    Copy Link
  • Greg Berlin 818 posts 634 karma points
    Jan 23, 2013 @ 19:34
    Greg Berlin
    0

    Really?  Nobody got any tips on this one?

    How about if i paid somebody to fix this for me?  Tell me how much you want and we can talk, payment via PayPal.

    Copy Link
  • damian vander wilt 12 posts 56 karma points
    Feb 21, 2013 @ 21:22
    damian vander wilt
    0

    Hi, just checking for possible duplicates to the post i'm about to add and this is somewhat related. -I hope you found your answer already and are on the road in good shape.

    I tried to replicate this issue and see if i might be able to help but i'm afraid that i was unable to recreate the same error.

     

    first i ran the following to delete the constraint:

    ALTER TABLE [dbo].[cmsPropertyData]

     DROP CONSTRAINT [FK_cmsPropertyData_cmsPropertyType]

     

    i then ran the following to add it back and it ran fine, with no errors ( other than the missing closing-paren from your example above)

    ALTER TABLE [dbo].[cmsPropertyData] WITH CHECK ADD CONSTRAINT [FK_cmsPropertyData_cmsPropertyType] FOREIGN KEY([propertytypeid]) REFERENCES [dbo].[cmsPropertyType] ([id])

    I didn't run the second statement as it is redundant - and so says sql manager.

    Perhaps i was unsuccesful at reproducing your issue as i've already done a number of other contraints updates and have so already resolved it(?). I am posting my constraints updates in the forum soon for approval by the community - to see if anybody else can point out that my updates are a bad idea for any reason that i haven't thought of yet. I'll try to remember and swing back here to add a link if you're interested.

     

     

     

     

     

    Copy Link
  • Paul Sørensen 304 posts 650 karma points
    Feb 21, 2013 @ 21:58
    Paul Sørensen
    0

    Hi

    what is the output of this

    SELECT * FROM dbo.cmsPropertyData WHERE propertytypeid NOT IN (SELECT id FROM dbo.cmsPropertyType)

    to get information on what the problem is

    /Paul S

    Copy Link
  • Greg Berlin 818 posts 634 karma points
    Feb 21, 2013 @ 22:21
    Greg Berlin
    0

    Hi Paul,

    Running this query:

    SELECT * FROM dbo.cmsPropertyData WHERE propertytypeid NOT IN (SELECT id FROM dbo.cmsPropertyType)

    Returns 690 rows, with propertyTypeIds of either 260 or 244.  All the 4 last columns are mostly null or 0 values.

    Running this returns no rows:

    select * from cmsPropertyType where cmsPropertyType.id in(244,260)

     

    Copy Link
  • Greg Berlin 818 posts 634 karma points
    Feb 21, 2013 @ 22:29
    Greg Berlin
    0

    Could this be related to this other rather strange issue i'm having, described here:

    http://our.umbraco.org/forum/ourumb-dev-forum/bugs/38481-Creating-page-doesnt-set-parent-node-correctly,-sometimes

    ??

    Copy Link
  • Greg Berlin 818 posts 634 karma points
    Feb 21, 2013 @ 22:31
    Greg Berlin
    0

    Doh!  Looking at the query i ran, i realise why it returned 0 results.  don't i feel sheepish. hehe

    Should i nuke all of those values in cmsPropertyData that don't have a corresponding propertyType?  I assume that's whats causing the constraint issues...?

    Copy Link
  • Paul Sørensen 304 posts 650 karma points
    Feb 23, 2013 @ 00:14
    Paul Sørensen
    0

    To enable the constraint it is nescessary

    /Paul S

    Copy Link
  • Greg Berlin 818 posts 634 karma points
    Feb 26, 2013 @ 19:53
    Greg Berlin
    100

    Cool... I deleted all the orphaned records using the following query:

    delete  FROM dbo.cmsPropertyData WHERE propertytypeid NOT IN(SELECT id FROM dbo.cmsPropertyType)

    Then added the constraint back using the following:

    ALTER TABLE [dbo].[cmsPropertyData] WITH CHECK 

    ADD CONSTRAINT [FK_cmsPropertyData_cmsPropertyType] 

    FOREIGN KEY([propertytypeid]) 

    REFERENCES [dbo].[cmsPropertyType]([id])

     All seems okay now, can't see any adverse effects from deleting those records.  

    Thanks for the help on this

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft