Change prices found by a compound key

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Update.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_POSTGRESQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_POSTGRESQL environment variable to your PostgreSQL connection string.");

await new RealCase3_ChangeAPriceListEntry().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Services;
using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Update.RealCases;

/// <summary>
/// The price list has no id column; each price is found by its region and SKU. Three scenarios: change one price by
/// the compound key; move a price to another SKU (changing part of the key, found by the old values through columns);
/// rename a natural id that other rows still use, which the foreign key refuses.
/// </summary>
public sealed class RealCase3_ChangeAPriceListEntry : ISample
{
    public string Name => "update-real-3-change-a-price-list-entry";

    public string Title => "Change prices found by a compound key";

    public async Task RunAsync(SampleContext dbContext)
    {
        var norway = new ArxEntity("country");
        norway["code"] = "NO";
        norway["name"] = "Norway";
        await dbContext.Service.CreateAsync(norway);

        var prices = new[] { "SKU-U501", "SKU-U502" }.Select(sku =>
        {
            var price = new ArxEntity("price_list");
            price["region"] = "NO";
            price["sku"] = sku;
            price["unit_price"] = 20.00m;
            price["valid_from"] = new DateOnly(2026, 1, 1);
            return price;
        }).ToList();
        await dbContext.Service.CreateMultipleAsync(prices);

        await Scenario1_OnePriceByItsKeyAsync(dbContext);

        await Scenario2_MoveAPriceToAnotherSkuAsync(dbContext);

        await Scenario3_RenameANaturalIdInUseAsync(dbContext);
    }

    /// <summary>1. Region and SKU together find the price: ArxMatch.Key with both columns.</summary>
    private static async Task Scenario1_OnePriceByItsKeyAsync(SampleContext dbContext)
    {
        Step("1. One price, by region and SKU");

        var price = new ArxEntity("price_list");
        price["region"] = "NO";
        price["sku"] = "SKU-U501";
        price["unit_price"] = 24.90m;
        await dbContext.Service.UpdateAsync(price, new ArxUpdateOptions { Match = ArxMatch.Key("region", "sku") });

        Show("NO / SKU-U501 is now 24.90");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT sku, unit_price
         * FROM price_list
         * WHERE region = 'NO' AND sku = 'SKU-U501';
         * -- Expect: 1 row, 24.90.
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. The SKU itself changes (the product was renumbered). The key columns can't both find the row and be written, so
    /// find it by other columns: here the region and its current price, with RequireSingleRow so a second match refuses it.
    /// </summary>
    private static async Task Scenario2_MoveAPriceToAnotherSkuAsync(SampleContext dbContext)
    {
        Step("2. Renumber a SKU: find the row by other columns");

        var move = new ArxEntity("price_list");
        move["region"] = "NO";
        move["unit_price"] = 20.00m;
        move["sku"] = "SKU-U502-NEW";
        await dbContext.Service.UpdateAsync(move, new ArxUpdateOptions { Match = ArxMatch.Columns("region", "unit_price"), RequireSingleRow = true });

        Show("SKU-U502 renumbered to SKU-U502-NEW");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT sku, unit_price
         * FROM price_list
         * WHERE region = 'NO'
         * ORDER BY sku;
         * -- Expect: SKU-U501 24.90, SKU-U502-NEW 20.00.
         * ===========================================================
         */
    }

    /// <summary>3. Renaming the country code NO would orphan its prices: the foreign key refuses it (ARX3002).</summary>
    private static async Task Scenario3_RenameANaturalIdInUseAsync(SampleContext dbContext)
    {
        Step("3. Rename a natural id that prices still use");

        var rename = new ArxEntity("country");
        rename["name"] = "Norway";
        rename["code"] = "NW";

        try
        {
            await dbContext.Service.UpdateAsync(rename, new ArxUpdateOptions { Match = ArxMatch.Columns("name") });
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.ForeignKeyViolation)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT code, name
         * FROM country
         * WHERE name = 'Norway';
         * -- Expect: 1 row, NO: unchanged.
         * ===========================================================
         */
    }
}

  > 1. One price, by region and SKU
      NO / SKU-U501 is now 24.90
  > 2. Renumber a SKU: find the row by other columns
      SKU-U502 renumbered to SKU-U502-NEW
  > 3. Rename a natural id that prices still use
      caught ArxDatabaseException: ARX3002 ForeignKeyViolation: Update on 'country' was refused: rows of 'price_list' still reference it (foreign key constraint 'price_list_region_fkey').