Primary Key Constraint Methods

Primary key constraint methods provide functionality for managing primary key constraints across all supported database providers. Primary keys ensure entity uniqueness and provide the foundation for referential integrity.

Quick Navigation

• Primary Key Existence Checking
  • DoesPrimaryKeyConstraintExistAsync - Check if table has a primary key
• Primary Key Creation
  • CreatePrimaryKeyConstraintIfNotExistsAsync (Model) - Create from DmPrimaryKeyConstraint model
  • CreatePrimaryKeyConstraintIfNotExistsAsync (Parameters) - Create with individual parameters
• Primary Key Discovery
  • GetPrimaryKeyConstraintAsync - Get table's primary key constraint
• Primary Key Deletion
  • DropPrimaryKeyConstraintIfExistsAsync - Drop primary key constraint

Primary Key Existence Checking

DoesPrimaryKeyConstraintExistAsync

Check if a primary key constraint exists on a table.

// Check if table has a primary key
bool hasPrimaryKey = await connection.DoesPrimaryKeyConstraintExistAsync("app", "employees");

if (hasPrimaryKey)
{
    Console.WriteLine("Table 'employees' has a primary key constraint");
    
    // Get the primary key details
    var pk = await connection.GetPrimaryKeyConstraintAsync("app", "employees");
    if (pk != null)
    {
        Console.WriteLine($"Primary key '{pk.ConstraintName}' on columns: {string.Join(", ", pk.Columns.Select(c => c.ColumnName))}");
    }
}
else
{
    Console.WriteLine("Table 'employees' has no primary key constraint");
    // Consider adding a primary key for referential integrity
}

// With transaction and cancellation
using var transaction = connection.BeginTransaction();
bool exists = await connection.DoesPrimaryKeyConstraintExistAsync(
    "app", 
    "employees", 
    tx: transaction,
    cancellationToken: cancellationToken
);

Parameters:

• schemaName - Schema containing the table
• tableName - Name of the table to check for primary key
• tx (optional) - Database transaction
• cancellationToken (optional) - Cancellation token

Returns: bool - true if table has a primary key constraint, false otherwise

Primary Key Creation

CreatePrimaryKeyConstraintIfNotExistsAsync (DmPrimaryKeyConstraint)

Create a primary key constraint only if the table doesn't already have one.

// Create primary key if table doesn't have one
bool created = await connection.CreatePrimaryKeyConstraintIfNotExistsAsync(primaryKey);

if (created)
{
    Console.WriteLine($"Primary key constraint '{primaryKey.ConstraintName}' was created");
}
else
{
    Console.WriteLine("Table already has a primary key constraint");
}

Parameters:

• constraint - DmPrimaryKeyConstraint model defining the constraint (includes SchemaName and TableName)
• tx (optional) - Database transaction
• cancellationToken (optional) - Cancellation token

Returns: bool - true if primary key was created, false if table already had a primary key

CreatePrimaryKeyConstraintIfNotExistsAsync (Parameters)

Create a primary key constraint using individual parameters for convenience.

// Simple single-column primary key
bool created = await connection.CreatePrimaryKeyConstraintIfNotExistsAsync(
    schemaName: "app",
    tableName: "employees",
    constraintName: "PK_Employees_Id",
    columns: new[] { new DmOrderedColumn("EmployeeId", DmColumnOrder.Ascending) }
);

// Multi-column composite primary key
bool created = await connection.CreatePrimaryKeyConstraintIfNotExistsAsync(
    "inventory",
    "product_warehouses", 
    "PK_ProductWarehouses_Composite",
    new[]
    {
        new DmOrderedColumn("ProductId", DmColumnOrder.Ascending),
        new DmOrderedColumn("WarehouseId", DmColumnOrder.Ascending)
    },
    tx: transaction,
    cancellationToken: cancellationToken
);

// Named constraint
bool created = await connection.CreatePrimaryKeyConstraintIfNotExistsAsync(
    "app",
    "categories",
    "PK_Categories_CategoryId",
    new[] { new DmOrderedColumn("CategoryId") }
);

Parameters:

• schemaName - Schema containing the table
• tableName - Name of the table to add primary key to
• constraintName - Name of the primary key constraint
• columns - Array of DmOrderedColumn defining key columns and order
• tx (optional) - Database transaction
• cancellationToken (optional) - Cancellation token

Returns: bool - true if primary key was created, false if table already had a primary key

Primary Key Discovery

GetPrimaryKeyConstraintAsync

Retrieve the primary key constraint for a table.

// Get primary key constraint for table
DmPrimaryKeyConstraint? primaryKey = await connection.GetPrimaryKeyConstraintAsync("app", "employees");

if (primaryKey != null)
{
    Console.WriteLine($"Primary key constraint details:");
    Console.WriteLine($"  Name: {primaryKey.ConstraintName}");
    Console.WriteLine($"  Columns: {string.Join(", ", primaryKey.Columns.Select(c => c.ColumnName))}");
    Console.WriteLine($"  Column count: {primaryKey.Columns.Count}");
    
    // Check if it's a composite key
    if (primaryKey.Columns.Count > 1)
    {
        Console.WriteLine("  This is a composite primary key");
    }
    
    // Show column order if available
    if (primaryKey.Columns.Any())
    {
        Console.WriteLine("  Column order:");
        foreach (var column in primaryKey.Columns)
        {
            Console.WriteLine($"    {column.ColumnName} ({column.Order})");
        }
    }
}
else
{
    Console.WriteLine("Table has no primary key constraint");
}

// With transaction
using var transaction = connection.BeginTransaction();
var pk = await connection.GetPrimaryKeyConstraintAsync("app", "employees", tx: transaction);

Parameters:

• schemaName - Schema containing the table
• tableName - Name of the table to get primary key from
• tx (optional) - Database transaction
• cancellationToken (optional) - Cancellation token

Returns: DmPrimaryKeyConstraint? - Primary key constraint model, or null if table has no primary key

Primary Key Deletion

DropPrimaryKeyConstraintIfExistsAsync

Remove a primary key constraint from a table if it exists.

// Drop primary key constraint if it exists
bool dropped = await connection.DropPrimaryKeyConstraintIfExistsAsync("app", "employees");

if (dropped)
{
    Console.WriteLine("Primary key constraint was dropped");
}
else
{
    Console.WriteLine("Table had no primary key constraint");
}

// With transaction
using var transaction = connection.BeginTransaction();
try
{
    bool dropped = await connection.DropPrimaryKeyConstraintIfExistsAsync("app", "employees", tx: transaction);
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Parameters:

• schemaName - Schema containing the table
• tableName - Name of the table containing the primary key
• constraintName (optional) - Name of the primary key constraint to drop
• tx (optional) - Database transaction
• cancellationToken (optional) - Cancellation token

Returns:

• DropPrimaryKeyConstraintAsync: Task - Completes when primary key is dropped
• DropPrimaryKeyConstraintIfExistsAsync: bool - true if primary key was dropped, false if none existed

Practical Examples

Primary Key Migration Patterns

public async Task MigrateToPrimaryKeyAsync(IDbConnection connection, string schema, string tableName)
{
    using var transaction = connection.BeginTransaction();
    try
    {
        // Check if table already has a primary key
        bool hasPK = await connection.DoesPrimaryKeyConstraintExistAsync(schema, tableName, tx: transaction);
        
        if (!hasPK)
        {
            // Add an identity column as primary key
            var idColumn = new DmColumn("Id", typeof(long))
            {
                SchemaName = schema,
                TableName = tableName,
                IsAutoIncrement = true,
                IsNullable = false
            };
            
            await connection.CreateColumnIfNotExistsAsync(idColumn, tx: transaction);
            
            // Create primary key constraint
            var primaryKey = new DmPrimaryKeyConstraint($"PK_{tableName}_Id", new[] { new DmOrderedColumn("Id") })
            {
                SchemaName = schema,
                TableName = tableName
            };
            await connection.CreatePrimaryKeyConstraintIfNotExistsAsync(primaryKey, tx: transaction);
            
            Console.WriteLine($"Added identity primary key to {tableName}");
        }
        else
        {
            Console.WriteLine($"Table {tableName} already has a primary key");
        }
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Composite Key Management

public async Task CreateJunctionTableAsync(IDbConnection connection, string schema, 
    string tableName, string leftTable, string leftColumn, string rightTable, string rightColumn)
{
    using var transaction = connection.BeginTransaction();
    try
    {
        // Create the junction table columns
        var leftCol = new DmColumn($"{leftTable}Id", typeof(int)) { IsNullable = false };
        var rightCol = new DmColumn($"{rightTable}Id", typeof(int)) { IsNullable = false };
        
        var columns = new[] { leftCol, rightCol };
        
        // Create composite primary key
        var compositePK = new DmPrimaryKeyConstraint(
            $"PK_{tableName}_Composite", 
            new[] { new DmOrderedColumn($"{leftTable}Id"), new DmOrderedColumn($"{rightTable}Id") }
        );
        
        // Create foreign key constraints
        var leftFK = new DmForeignKeyConstraint(
            $"FK_{tableName}_{leftTable}",
            new[] { new DmOrderedColumn($"{leftTable}Id") },
            leftTable,
            new[] { new DmOrderedColumn(leftColumn) }
        );
        
        var rightFK = new DmForeignKeyConstraint(
            $"FK_{tableName}_{rightTable}",
            new[] { new DmOrderedColumn($"{rightTable}Id") },
            rightTable,
            new[] { new DmOrderedColumn(rightColumn) }
        );
        
        // Create the table with all constraints
        bool created = await connection.CreateTableIfNotExistsAsync(
            schema,
            tableName,
            columns,
            compositePK,
            checkConstraints: null,
            defaultConstraints: null,
            uniqueConstraints: null,
            foreignKeyConstraints: new[] { leftFK, rightFK },
            indexes: null,
            tx: transaction
        );
        
        if (created)
        {
            Console.WriteLine($"Junction table '{tableName}' created with composite primary key");
        }
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Primary Key Analysis

public async Task AnalyzePrimaryKeysAsync(IDbConnection connection, string schema)
{
    var tables = await connection.GetTableNamesAsync(schema);
    
    Console.WriteLine($"Primary Key Analysis for schema '{schema}':");
    Console.WriteLine($"Total tables: {tables.Count}");
    
    var tablesWithPK = new List<string>();
    var tablesWithoutPK = new List<string>();
    var compositePKTables = new List<(string tableName, int columnCount)>();
    
    foreach (var tableName in tables)
    {
        bool hasPK = await connection.DoesPrimaryKeyConstraintExistAsync(schema, tableName);
        
        if (hasPK)
        {
            tablesWithPK.Add(tableName);
            
            var pk = await connection.GetPrimaryKeyConstraintAsync(schema, tableName);
            if (pk != null && pk.Columns.Count > 1)
            {
                compositePKTables.Add((tableName, pk.Columns.Count));
            }
        }
        else
        {
            tablesWithoutPK.Add(tableName);
        }
    }
    
    Console.WriteLine($"Tables with primary keys: {tablesWithPK.Count}");
    Console.WriteLine($"Tables without primary keys: {tablesWithoutPK.Count}");
    Console.WriteLine($"Tables with composite primary keys: {compositePKTables.Count}");
    
    if (tablesWithoutPK.Any())
    {
        Console.WriteLine("\nTables missing primary keys:");
        foreach (var table in tablesWithoutPK)
        {
            Console.WriteLine($"  ⚠️  {table}");
        }
    }
    
    if (compositePKTables.Any())
    {
        Console.WriteLine("\nTables with composite primary keys:");
        foreach (var (tableName, columnCount) in compositePKTables)
        {
            Console.WriteLine($"  📋 {tableName} ({columnCount} columns)");
        }
    }
    
    // Calculate primary key coverage
    var coverage = tables.Count > 0 ? (double)tablesWithPK.Count / tables.Count * 100 : 0;
    Console.WriteLine($"\nPrimary key coverage: {coverage:F1}%");
    
    if (coverage < 100)
    {
        Console.WriteLine("💡 Consider adding primary keys to tables without them for referential integrity");
    }
}

Primary Key Cleanup and Standardization

public async Task StandardizePrimaryKeyNamesAsync(IDbConnection connection, string schema)
{
    var tables = await connection.GetTableNamesAsync(schema);
    
    foreach (var tableName in tables)
    {
        var pk = await connection.GetPrimaryKeyConstraintAsync(schema, tableName);
        if (pk == null) continue;
        
        // Check if primary key name follows standard convention
        var expectedName = $"PK_{tableName}";
        if (pk.ConstraintName != expectedName)
        {
            Console.WriteLine($"Standardizing primary key name for {tableName}: '{pk.ConstraintName}' -> '{expectedName}'");
            
            using var transaction = connection.BeginTransaction();
            try
            {
                // Drop existing primary key
                await connection.DropPrimaryKeyConstraintIfExistsAsync(schema, tableName, tx: transaction);
                
                // Create new primary key with standard name
                var standardPK = new DmPrimaryKeyConstraint(expectedName, pk.Columns.ToArray())
                {
                    SchemaName = schema,
                    TableName = tableName
                };
                await connection.CreatePrimaryKeyConstraintIfNotExistsAsync(standardPK, tx: transaction);
                
                transaction.Commit();
                Console.WriteLine($"  ✅ Renamed to '{expectedName}'");
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                Console.WriteLine($"  ❌ Failed to rename: {ex.Message}");
            }
        }
    }
}

public async Task ValidatePrimaryKeyIntegrityAsync(IDbConnection connection, string schema)
{
    var tables = await connection.GetTablesAsync(schema);
    
    foreach (var table in tables)
    {
        var pk = table.PrimaryKeyConstraint;
        if (pk == null)
        {
            Console.WriteLine($"⚠️  Table '{table.TableName}' has no primary key");
            continue;
        }
        
        // Validate that all primary key columns exist and have correct properties
        foreach (var columnName in pk.Columns.Select(c => c.ColumnName))
        {
            var column = table.Columns.FirstOrDefault(c => c.ColumnName == columnName);
            if (column == null)
            {
                Console.WriteLine($"❌ Primary key column '{columnName}' not found in table '{table.TableName}'");
                continue;
            }
            
            if (column.IsNullable)
            {
                Console.WriteLine($"⚠️  Primary key column '{columnName}' in table '{table.TableName}' allows NULL values");
            }
        }
        
        Console.WriteLine($"✅ Primary key '{pk.ConstraintName}' on table '{table.TableName}' is valid");
    }
}

Primary key constraint methods provide essential functionality for maintaining data integrity and establishing the foundation for relational database design across all supported database providers.