Saat sedang membantu teman kantor yang progress projectnya dah mau kelar saya sedikit terkejut dengan DW(Datawarehouse) yang beliau bikin. Ternyata dari awal project sampai project hampir selesai dia belum memasang constraint(foreign key dan unique key) di DW. Awalnya memang sengaja tidak beliau pasang dengan alasan agar tidak lemot mengingat data yang dihandle cukup besar dengan resource yang kurang. Tetapi saya kurang setuju dengan alasan tersebut.

Constraint di DW sangatlah penting apalagi saat proses development untuk memastikan data yang masuk ke DW adalah data yang clean dan tidak redundan.

Akhirnya beliau minta saya untuk memasang constraint yang dibutuhkan di DW. Pada mulanya saya bermaksut membuat constraint yg dibutuhkan satu2 secara manual. Tapi beliau menyarankan untuk membuat generator saja mengingat jumlah constraint yang dibutuhkan lumayan banyak.

Untuk membuat generator ini tentunya ada beberapa aturan yang harus diperhatikan yaitu standarisasi penamaan tabel dan kolom pada DW. pada kesempatan kali ini saya sudah menyesuaikan generator yg telah saya buat dengan standar dari AdventureWorksDW dimana nama tabel diawali dengan kata Dim dan Fact untuk tabel dimensi dan fact. kemudian untuk kolom primary key dan foreign key di belakang nama kolomnya ada tambahan kata2 Key

Karena dibutuhkan 2 tipe constraint yaitu constraint untuk foreign key dan unique key maka saya membuat ke dua generatornya secara terpisah.

Berikut adalah script untuk men-generate constraint foreign key:

DECLARE @MainTable NVARCHAR(50)
DECLARE @ReferenceTable NVARCHAR(50)    
DECLARE @sqlFK varchar(max)
DECLARE @ColumnName NVARCHAR(50)
DECLARE @Count int
DECLARE @dummy int

DECLARE curCreateFK CURSOR  
FOR
    SELECT so.name,
           sc.name
    FROM   sysobjects so,
           syscolumns sc
    WHERE  so.id = sc.id
      AND  so.type = 'U'
      AND  so.name LIKE 'Fact%'
      AND  RIGHT(sc.name,3) = 'Key'
    ORDER BY
           so.name,
           sc.colorder
OPEN curCreateFK
    
FETCH NEXT FROM curCreateFK INTO @MainTable, @ColumnName
    
    
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Count = 0
    SELECT @Count = count(so.name)
    FROM   sysobjects so,
           syscolumns sc
    WHERE  so.id = sc.id
      AND  so.type = 'U'
      AND  so.name LIKE 'Dim%'
      AND  sc.isnullable=0
      AND  sc.name = @ColumnName
    
    IF @Count = 1
    BEGIN
        SELECT @ReferenceTable = so.name
        FROM   sysobjects so,
               syscolumns sc
        WHERE  so.id = sc.id
          AND  so.type = 'U'
          AND  so.name LIKE 'Dim%'
           AND  sc.isnullable=0
          AND  sc.name = @ColumnName

        SET @sqlFK =  'ALTER TABLE [dbo].[' + @MainTable + ']  WITH CHECK ADD  CONSTRAINT [FK_' + @MainTable +'_'+@ColumnName+'] FOREIGN KEY(['+@ColumnName+'])
        REFERENCES [dbo].[' + @ReferenceTable + '] ([' + @ColumnName +'])
        '

    END
    ELSE
    BEGIN
        IF @Count=0
        BEGIN
            IF @ColumnName LIKE '%Date%'
            BEGIN
                SET @sqlFK = 'ALTER TABLE [dbo].[' + @MainTable + ']  WITH CHECK ADD  CONSTRAINT [FK_' + @MainTable +'_'+@ColumnName+'] FOREIGN KEY(['+@ColumnName+'])
        REFERENCES [dbo].[DimTime] ([TimeKey])
                '
            END
            ELSE
            BEGIN
                -- Do it manually for different column name
                PRINT 'Match Not Found: ' + @MainTable + '-' + @ColumnName +'
                '
                SET @sqlFK=''
            END
            
        END
        ELSE
        BEGIN
            -- Found more than 1 primary key with the same name
            PRINT 'Double: ' + @MainTable + '-' + @ColumnName +'
            '
        END
        
    END

    BEGIN TRY           
        EXEC (@sqlFK)
        PRINT 'Success ' + @sqlFK
    END TRY
    BEGIN CATCH
        PRINT 'Already Exists ' + @sqlFK
    END CATCH      
    
    FETCH NEXT FROM curCreateFK INTO @MainTable, @ColumnName
END

CLOSE curCreateFK

DEALLOCATE curCreateFK

Dan ini generator untuk unique constraintnya:

 DECLARE curTable CURSOR FOR
SELECT so.name
FROM   sysobjects so
WHERE  so.type = 'U'
    AND  so.name LIKE 'Fact%'
ORDER BY
    so.name

DECLARE @tablename varchar(50),@UQ_Query varchar(max),@columnname varchar(50),@columncount int

OPEN curTable
FETCH NEXT FROM curTable INTO @tablename
WHILE @@fetch_status=0
BEGIN
    SET @columncount=0
    SET @UQ_Query='ALTER TABLE ['+@tablename+'] ADD CONSTRAINT [UQ_'+@tablename+'] UNIQUE ('
    DECLARE curColumn CURSOR FOR
    SELECT sc.name
    FROM sysobjects so,
        syscolumns sc
    WHERE  so.id = sc.id
        AND RIGHT(sc.name,3) = 'Key'
        AND so.name LIKE @tablename
    ORDER BY
        sc.colorder

    OPEN curColumn
    FETCH NEXT FROM curColumn INTO @columnname
    WHILE @@fetch_status=0
    BEGIN
        SET @columncount=@columncount+1
        IF @columncount=1
        BEGIN
             SET @UQ_Query=@UQ_Query+@columnname
        END
        ELSE
        BEGIN
            SET @UQ_Query=@UQ_Query+','+@columnname
        END
        
        FETCH NEXT FROM curColumn INTO @columnname
    END
    CLOSE curColumn
    DEALLOCATE curColumn
    
    --Exception Goes Here
    IF @tablename='FactInternetSales' OR @tablename='FactResellerSales' OR @tablename='FactInternetSalesReason'
    BEGIN
        SET @UQ_Query=@UQ_Query+',SalesOrderNumber,SalesOrderLineNumber'
    END
    
    SET @UQ_Query=@UQ_Query+')'
    
    BEGIN TRY
        EXEC (@UQ_Query)
        PRINT 'Success '+@UQ_Query
    END TRY
    BEGIN CATCH
        PRINT 'Already Exists '+@UQ_Query
    END CATCH
    
    FETCH NEXT FROM curTable INTO @tablename
END
CLOSE curTable
DEALLOCATE curTable