How can we help you today? How can we help you today?

Problem with unnamed contraints

We have problems when someone has creates a constraint in certain ways which causes the generation of a random name for the constraint (ie DF_Table_xx_A89FDC). ReadyRoll generates a DROP CONSTRAINT at the beginning of the script which fails at any site other than development (or at least any site where the constraint was created the same way).  We replace the errant drop with code similar to the following to fix this:

IF EXISTS (SELECT  o.name 
  FROM  sys.objects  o
  JOIN  sys.syscolumns  c  ON  c.cdefault = o.object_id
  WHERE  o.parent_object_id = OBJECT_ID(N'Rooms')
AND  o.type = 'D'
    AND  c.name = N'MediaDeliveryMethod')
BEGIN
DECLARE  @command NVARCHAR(MAX)

SELECT  @command = 'ALTER TABLE [dbo].[Rooms] DROP CONSTRAINT [' + o.name  + ']'
  FROM  sys.objects  o
  JOIN  sys.syscolumns  c  ON  c.cdefault = o.object_id
  WHERE  o.parent_object_id = OBJECT_ID(N'Rooms')
AND  o.type = 'D'
    AND  c.name = N'MediaDeliveryMethod';

EXECUTE  (@command);
END;
GO

Would it be possible to get ReadyRoll to generate code like this at least when it detects auto-generated constraint names? Also for primary keys it would be appreciated. (our template code looks different for primary keys)

RexJolliff
0

Comments

5 comments

Sort by
  • Eddie D
    Hi,
    Thank you for your Forum Post.

    My first thought on reading on your post, would be to simply set the Compare option to Ignore constraint Names and  / or Ignore system named constraint names.

    However, within Ready Roll, these options are by default turned off to ensure deployment reliability.  See the Unsupported Options section in this help article:
    https://documentation.red-gate.com/rr1/key-concepts/configuring-comparison-script-generation-options

    Alternatively you can create your own Pre and Post Deployment scripts using your code template to handle auto-generated constraint names:
    https://documentation.red-gate.com/rr1/key-concepts/include-scripts

    Many Thanks
    Eddie


    Eddie D
    0
  • way0utwest
    Honestly, what to do here is actually fix this as a part of build and fail the build if the constraint isn't named with doso standard. This for a couple things. Developers actively consider the constraint more and you also ensure no duplicate constraints get generated by using a standard 

    That being said, it would be nice for SCA to generate these according to some pattern and offer to fix your dev db
    way0utwest
    0
  • GurpreetGill
    Hi 

    Any workaround to this problem.

    Thanks
    Gill
    GurpreetGill
    0
  • way0utwest
    There is no workaround here. The solution is to name your constraints. They should be consistently named in all environments to ensure you can alter/drop them easily.
    way0utwest
    0
  • GurpreetGill
    ok, Thanks wayOutwest.
    GurpreetGill
    0

Add comment

Please sign in to leave a comment.