使用C#+Npgsql创建PostgreSQL多表失败，部分表未生成

问题背景 #

我是C#和SQL新手，用以下代码尝试在PostgreSQL中创建8个表，但只有前5个成功创建，后续抛出Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll异常，自查SQL语法没发现问题，求解决。

原代码：

public static bool TriggerDbMassAction_Branch()
{
    bool isCompleted;
    try
    {
        string createSalesTable = "CREATE TABLE IF NOT EXISTS Sales (InvoiceID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantitySold INT NOT NULL, SellingPrice FLOAT NOT NULL, SoldTo VARCHAR(255) NOT NULL, CustomerContactInfo VARCHAR(255) NOT NULL, SoldOn TIMESTAMP NOT NULL);";
        using var createSalesTableCommand = new NpgsqlCommand(createSalesTable, con);
        createSalesTableCommand.ExecuteScalar();

        string createPurchasesTable = "CREATE TABLE IF NOT EXISTS Purchases (InvoiceID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityBought INT NOT NULL, BuyingPrice FLOAT NOT NULL, PurchasedFrom VARCHAR(255) NOT NULL, SupplierContactInfo VARCHAR(255) NOT NULL, PurchasedOn TIMESTAMP NOT NULL);";
        using var createPurchasesTableCommand = new NpgsqlCommand(createPurchasesTable, con);
        createPurchasesTableCommand.ExecuteScalar();

        string createTransferInwardsTable = "CREATE TABLE IF NOT EXISTS TransferInwards (TransferID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityTransferred INT NOT NULL, TransferredFrom VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL, TransferredOn TIMESTAMP NOT NULL);";
        using var createTransferInwardsTableCommand = new NpgsqlCommand(createTransferInwardsTable, con);
        createTransferInwardsTableCommand.ExecuteScalar();

        string createTransferOutwardsTable = "CREATE TABLE IF NOT EXISTS TransferOutwards  (TransferID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityTransferred INT NOT NULL, TransferredTo VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL, TransferredOn TIMESTAMP NOT NULL);";
        using var createTransferOutwardsTableCommand = new NpgsqlCommand(createTransferOutwardsTable, con);
        createTransferOutwardsTableCommand.ExecuteScalar();

        string createReturnInwardsTable = "CREATE TABLE IF NOT EXISTS ReturnInwards (ReturnID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityReturned INT NOT NULL, ReturnedFrom VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL,   ReturnedOn TIMESTAMP NOT NULL);";
        using var createReturnInwardsTableCommand = new NpgsqlCommand(createReturnInwardsTable, con);
        createReturnInwardsTableCommand.ExecuteScalar();

        string createReturnOutwardsTable = "CREATE TABLE IF NOT EXISTS ReturnOutwards (ReturnID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) FORIEGN KEY NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityReturned INT NOT NULL, ReturnedTo VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL,    ReturnedOn TIMESTAMP NOT NULL);";
        using var createReturnOutwardsTableCommand = new NpgsqlCommand(createReturnOutwardsTable, con);
        createReturnOutwardsTableCommand.ExecuteScalar();

        string createBranchInventoryTable = "CREATE TABLE IF NOT EXISTS BranchInventory (ModelID VARCHAR(255) UNIQUE PRIMARY KEY NOT NULL, BrandID VARCHAR(255) NOT NULL, Description VARCHAR(255) NOT NULL, QuantityInStock INT NOT NULL, UnitPrice FLOAT NOT NULL, TotalWorth FLOAT NOT NULL);";
        using var createBranchInventoryTableCommand = new NpgsqlCommand(createBranchInventoryTable, con);
        createBranchInventoryTableCommand.ExecuteScalar();

        string createCommitHistoryTable = "CREATE TABLE IF NOT EXISTS CommitHistory (CommitID INT UNIQUE PRIMARY KEY NOT NULL, CommitDescription VARCHAR(255) NOT NULL, CommitDate TIMESTAMP NOT NULL, ApprovalStatus VARCHAR(255) NOT NULL,   ApprovalDate TIMESTAMP);";
        using var createCommitHistoryTableCommand = new NpgsqlCommand(createCommitHistoryTable, con);
        createCommitHistoryTableCommand.ExecuteScalar();

        isCompleted = true;
    }
    catch
    {
        isCompleted = false;
    }

    return isCompleted;
}

问题原因与修复 #

1. 拼写错误+无效外键语法 #

创建ReturnOutwards表的SQL中，FORIEGN是拼写错误，正确应为FOREIGN。同时PostgreSQL不支持单独声明FOREIGN KEY，必须明确指定关联的表和字段，比如关联BranchInventory的ModelID。

2. 表创建顺序错误 #

如果ReturnOutwards要关联BranchInventory，必须先创建BranchInventory，否则会因关联表不存在报错，原代码的创建顺序颠倒了。

3. 缺失异常详情捕获 #

原catch块没有捕获具体异常信息，无法快速定位问题，建议添加针对性异常捕获。

修正后的代码 #

public static bool TriggerDbMassAction_Branch()
{
    bool isCompleted;
    try
    {
        // 创建Sales表
        string createSalesTable = "CREATE TABLE IF NOT EXISTS Sales (InvoiceID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantitySold INT NOT NULL, SellingPrice FLOAT NOT NULL, SoldTo VARCHAR(255) NOT NULL, CustomerContactInfo VARCHAR(255) NOT NULL, SoldOn TIMESTAMP NOT NULL);";
        using var createSalesTableCommand = new NpgsqlCommand(createSalesTable, con);
        createSalesTableCommand.ExecuteScalar();

        // 创建Purchases表
        string createPurchasesTable = "CREATE TABLE IF NOT EXISTS Purchases (InvoiceID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityBought INT NOT NULL, BuyingPrice FLOAT NOT NULL, PurchasedFrom VARCHAR(255) NOT NULL, SupplierContactInfo VARCHAR(255) NOT NULL, PurchasedOn TIMESTAMP NOT NULL);";
        using var createPurchasesTableCommand = new NpgsqlCommand(createPurchasesTable, con);
        createPurchasesTableCommand.ExecuteScalar();

        // 创建TransferInwards表
        string createTransferInwardsTable = "CREATE TABLE IF NOT EXISTS TransferInwards (TransferID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityTransferred INT NOT NULL, TransferredFrom VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL, TransferredOn TIMESTAMP NOT NULL);";
        using var createTransferInwardsTableCommand = new NpgsqlCommand(createTransferInwardsTable, con);
        createTransferInwardsTableCommand.ExecuteScalar();

        // 创建TransferOutwards表
        string createTransferOutwardsTable = "CREATE TABLE IF NOT EXISTS TransferOutwards  (TransferID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityTransferred INT NOT NULL, TransferredTo VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL, TransferredOn TIMESTAMP NOT NULL);";
        using var createTransferOutwardsTableCommand = new NpgsqlCommand(createTransferOutwardsTable, con);
        createTransferOutwardsTableCommand.ExecuteScalar();

        // 创建ReturnInwards表
        string createReturnInwardsTable = "CREATE TABLE IF NOT EXISTS ReturnInwards (ReturnID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityReturned INT NOT NULL, ReturnedFrom VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL, ReturnedOn TIMESTAMP NOT NULL);";
        using var createReturnInwardsTableCommand = new NpgsqlCommand(createReturnInwardsTable, con);
        createReturnInwardsTableCommand.ExecuteScalar();

        // 先创建BranchInventory表（被外键关联的表需提前创建）
        string createBranchInventoryTable = "CREATE TABLE IF NOT EXISTS BranchInventory (ModelID VARCHAR(255) UNIQUE PRIMARY KEY NOT NULL, BrandID VARCHAR(255) NOT NULL, Description VARCHAR(255) NOT NULL, QuantityInStock INT NOT NULL, UnitPrice FLOAT NOT NULL, TotalWorth FLOAT NOT NULL);";
        using var createBranchInventoryTableCommand = new NpgsqlCommand(createBranchInventoryTable, con);
        createBranchInventoryTableCommand.ExecuteScalar();

        // 修正外键拼写与语法，关联BranchInventory的ModelID
        string createReturnOutwardsTable = "CREATE TABLE IF NOT EXISTS ReturnOutwards (ReturnID INT UNIQUE PRIMARY KEY NOT NULL, ModelID VARCHAR(255) NOT NULL, BrandID VARCHAR(255) NOT NULL, QuantityReturned INT NOT NULL, ReturnedTo VARCHAR(255) NOT NULL, SignedBy VARCHAR(255) NOT NULL, ReturnedOn TIMESTAMP NOT NULL, FOREIGN KEY (ModelID) REFERENCES BranchInventory(ModelID));";
        using var createReturnOutwardsTableCommand = new NpgsqlCommand(createReturnOutwardsTable, con);
        createReturnOutwardsTableCommand.ExecuteScalar();

        // 创建CommitHistory表
        string createCommitHistoryTable = "CREATE TABLE IF NOT EXISTS CommitHistory (CommitID INT UNIQUE PRIMARY KEY NOT NULL, CommitDescription VARCHAR(255) NOT NULL, CommitDate TIMESTAMP NOT NULL, ApprovalStatus VARCHAR(255) NOT NULL, ApprovalDate TIMESTAMP);";
        using var createCommitHistoryTableCommand = new NpgsqlCommand(createCommitHistoryTable, con);
        createCommitHistoryTableCommand.ExecuteScalar();

        isCompleted = true;
    }
    catch (Npgsql.PostgresException ex)
    {
        // 输出PostgreSQL专属错误信息，方便调试
        Console.WriteLine($"PostgreSQL错误: {ex.Message}, SQL状态码: {ex.SqlState}");
        isCompleted = false;
    }
    catch (Exception ex)
    {
        Console.WriteLine($"其他错误: {ex.Message}");
        isCompleted = false;
    }

    return isCompleted;
}

内容的提问来源于stack exchange，提问作者cfdaniel