SqlLabDataGenerator

2.0.0

internal/functions/validation/Test-SldgForeignKeyIntegrity.ps1

function Test-SldgForeignKeyIntegrity {
    <#
    .SYNOPSIS
        Validates foreign key referential integrity in generated data.
    #>
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [SqlLabDataGenerator.Connection]$ConnectionInfo,

        [Parameter(Mandatory)]
        [SqlLabDataGenerator.SchemaModel]$SchemaModel
    )

    $results = [System.Collections.Generic.List[object]]::new()

    foreach ($table in $SchemaModel.Tables) {
        foreach ($fk in $table.ForeignKeys) {
            Write-PSFMessage -Level Verbose -Message ($script:strings.'Validation.FKCheck' -f $table.SchemaName, $table.TableName)

            $parentName = Get-SldgSafeSqlName -SchemaName $table.SchemaName -TableName $table.TableName -ConnectionInfo $ConnectionInfo
            $parentCol = Get-SldgSafeSqlName -ColumnName $fk.ParentColumn -ConnectionInfo $ConnectionInfo
            $refName = Get-SldgSafeSqlName -SchemaName $fk.ReferencedSchema -TableName $fk.ReferencedTable -ConnectionInfo $ConnectionInfo
            $refCol = Get-SldgSafeSqlName -ColumnName $fk.ReferencedColumn -ConnectionInfo $ConnectionInfo

            $query = @"
SELECT COUNT(*) AS OrphanCount
FROM $parentName child
WHERE child.$parentCol IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM $refName parent
    WHERE parent.$refCol = child.$parentCol
  )
"@

            $conn = $ConnectionInfo.DbConnection
            $cmd = $conn.CreateCommand()
            $cmd.CommandText = $query
            $cmd.CommandTimeout = Get-PSFConfigValue -FullName 'SqlLabDataGenerator.Database.SchemaTimeout'
            try {
                $orphanCount = $cmd.ExecuteScalar()
            }
            finally {
                $cmd.Dispose()
            }

            $passed = $orphanCount -eq 0
            $results.Add([SqlLabDataGenerator.ValidationResult]@{
                CheckType        = 'ForeignKey'
                TableName        = $table.FullName
                ConstraintName   = $fk.ForeignKeyName
                Column           = $fk.ParentColumn
                ReferencedTable  = "$($fk.ReferencedSchema).$($fk.ReferencedTable)"
                ReferencedColumn = $fk.ReferencedColumn
                Passed           = $passed
                Severity         = if ($passed) { 'OK' } else { 'Error' }
                Details          = if ($passed) { 'All references valid' } else { "$orphanCount orphaned rows found" }
            })

            if (-not $passed) {
                Write-PSFMessage -Level Warning -Message ($script:strings.'Validation.FKViolation' -f $table.SchemaName, $table.TableName, $fk.ParentColumn, $fk.ReferencedSchema, $fk.ReferencedTable, $fk.ReferencedColumn, $orphanCount)
            }
        }
    }

    $results
}