Add dummy rows to a table to enforce primary/foreign key constraint

Sometimes the data is not what you want it to be. Or rather the quality is not what you want it to be. What I am referring to in this case is when you have a primary key and you want to reference that key from another table by a foreign key constraint but for some reason the foreign key is NULL and you don’t want NULL as a primary key in that table. Sure you can add a dummy row manually to your table with a value of '-1' or whatever you like. But what if you TRUNCATE your table before each load? Should you add the row manually every time? I’m sure that there’s hundreds of solutions  to this problem but below is how I choose to solve my particular problem. 

usp_GenerateInsertScript

/* 
 * ======================================================================
 * Author: mattssok.com
 * Desc:   This procedure generates a dummy row for the non-nullable 
 *         columns and inserts it into @TableName. 
 *
 * Exec:   EXEC [ctr].[usp_GenerateInsertScript] '[dbo].[TableName]'
 * ======================================================================
 */
ALTER procedure [ctr].[usp_GenerateInsertScript]
(
    @TableName nvarchar(250)
)
AS

-- Error handling reference: http://www.sommarskog.se/error_handling/Part1.html
SET XACT_ABORT, NOCOUNT ON

BEGIN TRY
  BEGIN TRANSACTION

    DECLARE @columns       nvarchar(MAX)
           ,@valueCols     nvarchar(MAX)
           ,@valueComplete nvarchar(MAX)
           ,@insertInto    nvarchar(MAX)
           ,@execSql       nvarchar(MAX)

    -- Retrieve the list of columns in the target table
    SELECT 
      @columns = 
	    STUFF
        (
          (
            SELECT 
              ',[' + a.name + ']' 
            FROM 
              sys.all_columns a 
            WHERE 
              a.OBJECT_ID = OBJECT_ID(@TableName) 
              AND is_nullable = 0
              AND is_identity <> 1 
            FOR XML PATH('')
          ), 1, 1, ''
        )

    SELECT 
      @insertInto = 'INSERT INTO ' + @TableName + '(' + @columns + ')'

    -- Retrieve list of insert values depending on the column type
    SELECT 
      @valueCols = 
	    STUFF
        (
          (
            SELECT 
              CASE
              -- generate corresponding insert values for each of these 
              -- column types
                WHEN b.name = 'nvarchar'  THEN ', ''-1''' 
                WHEN b.name = 'char'      THEN ', ''0''' 
                WHEN b.name = 'int'       THEN ', 0'
                WHEN b.name = 'smallint'  THEN ', 0'
                WHEN b.name = 'tinyint'   THEN ', 0'
                WHEN b.name = 'bigint'    THEN ', 0'
                WHEN b.name = 'datetime2' THEN ', ''9999-01-01'''
              END 
            FROM 
              sys.all_columns a 
            INNER JOIN 
              sys.types b ON ( a.system_type_id = b.system_type_id 
                              AND a.user_type_id = b.user_type_id )
            WHERE 
              a.OBJECT_ID = OBJECT_ID(@TableName) 
              AND a.is_nullable = 0
              AND is_identity != 1 
            FOR XML PATH('')
          ),1,1,''
        )

    -- Create the values clause
    SET @valueComplete = ' VALUES (' + @valueCols + ')'
    SET @execSql = @insertInto + @valueComplete

    --PRINT @execSql
    EXECUTE (@execSql)

  COMMIT TRANSACTION -- End of transaction
  
END TRY -- End of try
 
BEGIN CATCH
   IF @@TRANCOUNT > 0 
  ROLLBACK TRANSACTION
  -- Error handling reference: http://www.sommarskog.se/error_handling/Part1.html
  EXEC ctr.usp_error_handler    
  RETURN 55555
END CATCH

Output

The output from this procedure will be a statement that looks like this:

INSERT INTO [dbo].[TableName]([NonNullableColumn1],[NonNullableColumn2],[NonNullableColumn3],[NonNullableColumn4],[NonNullableColumn5) VALUES /*these depend on the column type*/( '-1', '-1', '-1', '-1', 0)

which then will be executed.   

That is all for this time. Hopefully this can help someone out there.

//mattssok

mattssok

Leave a Reply

Your email address will not be published. Required fields are marked *