Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-10854

sql server drop default constraint when dropping column

    Details

    • Type: Improvement
    • Status: Unverified Fix
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.0.10, 3.1.0-dev
    • Fix Version/s: 3.0.12-RC1
    • Labels:
      None
    • Environment:
      SQL Server 2008 R2 (express and enterprise), zend server 5.6, phpbb 3.0.10

      Description

      when using UMIL with sql server then the script will break when dropping column, mentioning an error like this in UMIL.

      Msg 5074, Level 16, State 1, Line 1
      The object 'DF_RproleIsClo_4BCC3ABA' is dependent on column 'IsClosed'.
      Msg 4922, Level 16, State 9, Line 1
      ALTER TABLE DROP COLUMN IsClosed failed because one or more objects access this column.

      This happens because of "DEFAULT" constraints which is always the case when not adding one specifically
      see http://stackoverflow.com/questions/314998/sql-server-2005-drop-column-with-constraints

      solution is to drop default constraint before dropping column. in Oracle this is simply "DROP COLUMN xxx CASCADE CONSTRAINTS" but in SQL server the only solutions seems to be a T-SQL script.

        Activity

        Hide
        Sajaki Sajaki added a comment -
        Show
        Sajaki Sajaki added a comment - i added a pull request https://github.com/phpbb/phpbb3/pull/777
        Hide
        Sajaki Sajaki added a comment -

        pull request

        Show
        Sajaki Sajaki added a comment - pull request
        Hide
        Noxwizard Patrick Webster added a comment - - edited

        You should probably adapt the query from the selected answer in that stackoverflow topic instead of using a T-SQL script. i.e:

        SELECT col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, 'IsDefaultCnst') AS is_defcnst, dobj.name AS def_name
        FROM sys.columns col 
            LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
        WHERE col.object_id = object_id('{$table_name}') 
        AND col.name = '{$group_id}'
        AND dobj.name IS NOT NULL

        Then generate queries to drop each constraint and finally the column.

        Show
        Noxwizard Patrick Webster added a comment - - edited You should probably adapt the query from the selected answer in that stackoverflow topic instead of using a T-SQL script. i.e: SELECT col.name, col.column_id, col.default_object_id, OBJECTPROPERTY(col.default_object_id, 'IsDefaultCnst') AS is_defcnst, dobj.name AS def_name FROM sys.columns col LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D') WHERE col.object_id = object_id('{$table_name}') AND col.name = '{$group_id}' AND dobj.name IS NOT NULL Then generate queries to drop each constraint and finally the column.

          People

          • Assignee:
            EXreaction EXreaction [X] (Inactive)
            Reporter:
            Sajaki Sajaki
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development