"Error creating multiple tables in database using C# code"

Question

I was trying to create multiple tables in my database with this c# code:

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;
}

The code attempts to create several tables including Sales, Purchases, TransferInwards, TransferOutwards, ReturnInwards, ReturnOutwards, inventory and commits. Each table has its own set of columns and constraints.

However, when executing the code, I am facing an error message or unexpected behavior. Here are the details of the error:

The error message says "Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll". The specific problem seems to be related to the table definition or the SQL syntax.

I haven't really done anything sql before and I'm new to c# but I have already checked the code and ensured that the table definitions and SQL syntax are correct. I am using NpgsqlCommand to execute the SQL queries.

I would appreciate any insights or suggestions on how to resolve this issue and successfully create the multiple tables in my database. Thank you in advance for your help!

5 of the tables where created but when creating the rest I got this error:

Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll

These are the tables that were created: Sales, Purchases, TransferInwards, TransferOutwards, ReturnInwards. the rest weren't.

Answer 1

Your code executed successfully until returnoutwards table. Because, in that table you tried to create a foreign key, but you did not gave a reference.

For example;

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

For your returnoutwards table you need to decide which table gives reference to modelid column in returnoutwards table or if that is a mistake you need to delete it.