Smart Rename fails when column participates in check

fabianc2kfabianc2k Posts: 8
edited July 31, 2007 10:29AM in SQL Refactor Previous Versions
A check constraint on a table references a function, which references the same table.

In particular, the function references the column being renamed.

SQL Refactor scripted the change to the function before the change to the table, resulting in a fail.

Modifying the produced change script so that the function alteration occurs after the column rename makes the script function without problem.

Comments

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Unfortunately I am to recreate the reported fault symptoms, as per my example below. Can you please reply back with further details.

    Many Thanks
    Eddie Davis
    Red Gate Software Ltd
    Technical Support
    E-Mail: support@red-gate.com

    /*
    Script created by SQL Refactor version 1.1.0.97 from Red Gate Software Ltd at 30/07/2007 16:43:50
    Run this script on Sample to perform the Smart Rename refactoring.

    Please back up your database before running this script.
    */
    -- Summary for the smart rename:
    --
    -- Action Plan:
    -- Drop check constraint [order_check] from table [dbo].[Sales]
    -- Alter table [dbo].[Sales]
    -- Alter function [dbo].[customer_orders]
    -- Add constraint [order_check] to [dbo].[Sales]
    --
    -- No warnings
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Dropping constraints from [dbo].[Sales]'
    GO
    ALTER TABLE [dbo].[Sales] DROP CONSTRAINT [order_check]
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Altering [dbo].[Sales]'
    GO
    sp_rename N'[dbo].[Sales].[Ship_Date]', N'Shipping_Date', 'COLUMN'
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Altering [dbo].[customer_orders]'
    GO
    ALTER FUNCTION dbo.customer_orders(@pr_number INT)
    RETURNS TABLE
    AS RETURN(SELECT Shipping_Date AS Ship_Date
    FROM dbo.customer, dbo.Sales
    WHERE customer.cust_no = Sales.cust_no
    AND Order_no = @pr_number)
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Adding constraints to [dbo].[Sales]'
    GO
    ALTER TABLE [dbo].[Sales] ADD CONSTRAINT [order_check] CHECK (([Order_Date]<=Shipping_Date))
    GO
    @TRANCOUNT&gt;0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    @TRANCOUNT&gt;0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Steps to reproduce:

    1. Run create table / function script below
    2. Attempt to use smart rename on a column ColumnTwo

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS &#40;SELECT * FROM sys.objects WHERE object_id = OBJECT_ID&#40;N'&#91;dbo&#93;.&#91;FunctionTableProblem_Function&#93;'&#41; AND type in &#40;N'FN', N'IF', N'TF', N'FS', N'FT'&#41;&#41;
    BEGIN
    execute dbo.sp_executesql @statement = N'-- =============================================
    -- Author:		Fabian Chessell
    -- Create date: 
    -- Description:	
    -- =============================================
    CREATE FUNCTION &#91;dbo&#93;.&#91;FunctionTableProblem_Function&#93;
        &#40;
          @columnOne int,
          @columnTwo int 
        &#41;
    RETURNS bit
    AS BEGIN
        DECLARE @Result bit
    
    	-- This is an example function to reproduce the refactor bug
    	--
    	-- This function performs a uniqueness check, and returns
    	-- true if the value of column two has already been used for
    	-- a different row
    
        SET @Result = CASE WHEN EXISTS &#40; SELECT 1
                                         FROM   FunctionTableProblem_Table
                                         WHERE  ColumnTwo = @columnTwo
                                                AND NOT ColumnOne = @columnOne &#41;
                           THEN ''true''
                           ELSE ''false''
                      END
    
        RETURN @Result
    
       END
    ' 
    END
    
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS &#40;SELECT * FROM sys.objects WHERE object_id = OBJECT_ID&#40;N'&#91;dbo&#93;.&#91;FunctionTableProblem_Table&#93;'&#41; AND type in &#40;N'U'&#41;&#41;
    BEGIN
    CREATE TABLE &#91;dbo&#93;.&#91;FunctionTableProblem_Table&#93;&#40;
    	&#91;ColumnOne&#93; &#91;int&#93; IDENTITY&#40;1,1&#41; NOT NULL,
    	&#91;ColumnTwo&#93; &#91;int&#93; NOT NULL,
     CONSTRAINT &#91;PK_FunctionTableProblem&#93; PRIMARY KEY CLUSTERED 
    &#40;
    	&#91;ColumnOne&#93; ASC
    &#41;WITH &#40;PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON&#41; ON &#91;PRIMARY&#93;
    &#41; ON &#91;PRIMARY&#93;
    END
    GO
    IF NOT EXISTS &#40;SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID&#40;N'&#91;dbo&#93;.&#91;CK_FunctionTableProblem_Table&#93;'&#41; AND parent_object_id = OBJECT_ID&#40;N'&#91;dbo&#93;.&#91;FunctionTableProblem_Table&#93;'&#41;&#41;
    ALTER TABLE &#91;dbo&#93;.&#91;FunctionTableProblem_Table&#93;  WITH CHECK ADD  CONSTRAINT &#91;CK_FunctionTableProblem_Table&#93; CHECK  &#40;&#40;&#91;dbo&#93;.&#91;FunctionTableProblem_Function&#93;&#40;&#91;ColumnOne&#93;,&#91;ColumnTwo&#93;&#41;='false'&#41;&#41;
    GO
    ALTER TABLE &#91;dbo&#93;.&#91;FunctionTableProblem_Table&#93; CHECK CONSTRAINT &#91;CK_FunctionTableProblem_Table&#93;
    
    [/code]
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Thank you very much for your reply.

    Using your code I reproduced the reported fault symptoms, where the function is incorrectly altered before the table.

    As per your orignal post, changing the dependency order so that the table is altered before function resolves the issue.


    I will feed this information onto our Development and wait for the outcome of their investigations.

    Many Thanks
    Eddie

    Eddie davis
    Red Gate Software Ltd
    Technical Support
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.