Append-only ledger tables: are FOREIGN KEY, UNIQUE and CHECK constraints supported and enforced? Concurrency and drop permissions

Bryan Dabbelt 0 Reputation points
2026-11-21T04:23:30.4833333+00:00

We're evaluating Azure SQL Database append-only ledger tables (WITH (LEDGER = ON (APPEND_ONLY = ON))) for a hash-linked audit log. The design relies on:

  • a self-referencing FOREIGN KEY;
  • a primary key or UNIQUE constraint;
  • a CHECK constraint;
  • the runtime principal being able only to INSERT and SELECT.

The ledger documentation we've found (ledger overview, append-only ledger tables, ledger limits, CREATE TABLE) doesn't say whether these constraints are allowed on append-only ledger tables. We don't want to assume ordinary-table behaviour carries over. A link to the official documentation stating each answer would be ideal.

  1. Are FOREIGN KEY constraints (including a self-referencing foreign key), UNIQUE constraints and indexes, and CHECK constraints supported and enforced on append-only ledger tables? Can a ledger table be both the referencing and the referenced table?
  2. Can such constraints be dropped or altered after creation (ALTER TABLE … DROP CONSTRAINT), and by which permissions?
  3. Under concurrent transactions, is a primary-key or foreign-key violation on insert into an append-only ledger table guaranteed to abort one of two conflicting inserts?
  4. Are reads on the primary replica strongly consistent across clients, and how is a connection guaranteed not to be routed to a readable secondary?
  5. Which roles can drop a ledger database, or the logical server? Is point-in-time restore always to a new database?

Thank you.

Azure SQL Database
0 comments No comments

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 137.9K Reputation points MVP Volunteer Moderator
    2026-11-21T13:58:13.6833333+00:00
    1. Are FOREIGN KEY constraints (including a self-referencing foreign key), UNIQUE constraints and indexes, and CHECK constraints supported and enforced on append-only ledger tables? Can a ledger table be both the referencing and the referenced table?

    Yes. See script below. There are several batches that are commented out. These batches fail if you uncomment them. They demonstrate some things you cannot do. For instance an FK constraint on column in a ledger table must have ON CASCADE NO ACTION (which is the default).

    1. Can such constraints be dropped or altered after creation (ALTER TABLE … DROP CONSTRAINT), and by which permissions?

    Yes, as demonstrated by the script. The permissions required are the same as for any other DDL operation. That is, you need ALTER permission on the table. Which you will have if you are a member of db_owner or db_ddladmin.

    1. Under concurrent transactions, is a primary-key or foreign-key violation on insert into an append-only ledger table guaranteed to abort one of two conflicting inserts?

    Yes. This is no different from any other table.

    1. Are reads on the primary replica strongly consistent across clients,

    Two clients that submit the same query at exactly the same time are expected to get the same result. However, there is no guarantee. Due to scheduling etc, one of the processes may be delayed, and in the meanwhile a transaction may have been committed that causes the second client to get a different result. Here I am assuming that the database has READ_COMMITTED_SNAPSHOT enabled, as this is the default in Azure SQL Database. Not that there would be any guarantee even if you disabled it, but it is more prone to happen with READ_COMMITTED_SNAPSHOT.

    and how is a connection guaranteed not to be routed to a readable secondary?

    A connection can only be routed to a readable secondary if it has Application Intent=ReadOnly in the connection string.

    1. Which roles can drop a ledger database, or the logical server?

    The same roles that can drop a regular database. (I'm sorry, but I don't know these roles on the top of my head.)

    Is point-in-time restore always to a new database?

    Yes. You certainly do not want a restore of the database at an earlier point in time to over-write the existing one when you have an insert-only ledger. I will need to add the disclaimer that I have not tested.

    Here is my demo script. It has ROLLBACK at the end, as I did not want to have a permanent ledger table in my database.

    DROP TABLE IF EXISTS Objects
    SELECT * INTO Objects FROM sys.objects
    ALTER TABLE Objects ADD CONSTRAINT pk_Objects PRIMARY KEY (object_id)
    go
    BEGIN TRANSACTION
    
    CREATE TABLE insert_only_ledger (entry_id       int NOT NULL,
                                     object_id      int NOT NULL,
                                     somedata       nvarchar(50) NOT NULL,
                                     ref_entry_id   int NULL,
                                     entry_type     tinyint CONSTRAINT ck_entry_type CHECK (entry_type BETWEEN 1 AND 5),
                                     start_trans_id bigint GENERATED ALWAYS AS TRANSACTION_ID START NOT NULL,
                                     start_seq_no   bigint GENERATED ALWAYS AS SEQUENCE_NUMBER START NOT NULL,
                                     CONSTRAINT pk_insert_only_ledger PRIMARY KEY (entry_id)
    )
    WITH (
        LEDGER = ON (
            LEDGER_VIEW = dbo.insert_only_ledger_view (
                TRANSACTION_ID_COLUMN_NAME = trans_id,
                SEQUENCE_NUMBER_COLUMN_NAME = seq_no,
                OPERATION_TYPE_COLUMN_NAME = op_id,
                OPERATION_TYPE_DESC_COLUMN_NAME = op_name
            ),
        APPEND_ONLY= ON));
    go
    --ALTER TABLE insert_only_ledger ADD CONSTRAINT fk_ledger_object 
    --   FOREIGN KEY (object_id) REFERENCES dbo.Objects(object_id)
    --   ON DELETE CASCADE
    go
    ALTER TABLE insert_only_ledger ADD CONSTRAINT fk_ledger_object 
       FOREIGN KEY (object_id) REFERENCES dbo.Objects(object_id)
    ALTER TABLE insert_only_ledger ADD CONSTRAINT fk_ledger_self 
       FOREIGN KEY (ref_entry_id) REFERENCES dbo.insert_only_ledger(entry_id)
    go
    --INSERT insert_only_ledger (entry_id, object_id, somedata, entry_type)
    --   SELECT 1, object_id, cast(newid() AS uniqueidentifier), 1
    --   FROM   sys.objects
    go
    -- INSERT insert_only_ledger (entry_id, object_id, somedata, entry_type)
    --   SELECT row_number() OVER(ORDER BY object_id), object_id + 9, cast(newid() AS uniqueidentifier), 1
    --   FROM   sys.objects
    go
    INSERT insert_only_ledger (entry_id, object_id, somedata, entry_type)
       SELECT row_number() OVER(ORDER BY object_id), object_id, cast(newid() AS uniqueidentifier), 1
       FROM   dbo.Objects
    go
    -- INSERT insert_only_ledger (entry_id, object_id, ref_entry_id, somedata, entry_type)
    --   SELECT isnull(MAX(entry_id), 0) + 1, 8, -99, 'Bludder', 5
    --   FROM   dbo.insert_only_ledger
    go
    INSERT insert_only_ledger (entry_id, object_id, ref_entry_id, somedata, entry_type)
       SELECT isnull(MAX(entry_id), 0) + 1, 8, 49, 'Bludder', 5
       FROM   dbo.insert_only_ledger
    go
    SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1 OR is_disabled = 1
    go
    ALTER TABLE insert_only_ledger DROP CONSTRAINT  ck_entry_type
    ALTER TABLE insert_only_ledger DROP CONSTRAINT  fk_ledger_self
    ALTER TABLE insert_only_ledger DROP CONSTRAINT  fk_ledger_object
    ALTER TABLE insert_only_ledger DROP CONSTRAINT  pk_insert_only_ledger
    go
    -- ALTER TABLE insert_only_ledger ALTER COLUMN entry_id bigint NOT NULL
    go
    ROLLBACK TRANSACTION
    ``
    

    Was this answer helpful?

    0 comments No comments

  2. AI answer

    2026-11-21T04:24:44.68+00:00

    The published ledger documentation does not explicitly guarantee that PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK constraints are supported on append-only ledger tables, so this design should not treat the generic Azure SQL constraint documentation as a ledger-specific contract without obtaining confirmation from Microsoft Support.

    Constraints on append-only ledger tables

    The documentation establishes these separate facts:

    • An append-only ledger table permits INSERT, but not UPDATE or DELETE; it cannot later be converted to a non-ledger table.^1^
    • Azure SQL Database ordinarily enforces:
      • foreign keys as referential-integrity constraints,^2^
      • UNIQUE constraints through corresponding unique indexes,^3^
      • CHECK expressions by rejecting values that evaluate to FALSE.^4^
    • The normal Azure SQL ALTER TABLE grammar supports PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK; primary and unique constraints generate indexes, while foreign keys do not automatically create indexes.^5^

    However, none of those pages explicitly says that all these constraints are valid on a table created with:

    WITH (LEDGER = ON (APPEND_ONLY = ON))
    

    The ledger limitations page documents supported schema changes such as adding nullable columns, restricted column alterations, and soft deletion of ledger columns/tables, but the supplied text does not address adding, dropping, disabling, or enforcing relational constraints.^6^

    Consequently, the following remain undocumented in the available official material:

    • whether an append-only ledger table can be both sides of an FK;
    • whether a self-referencing FK is accepted;
    • whether constraints can be added after ledger-table creation;
    • whether every normal index type is supported;
    • whether a constraint may be disabled and re-enabled.

    A deployment-time test can identify current engine behavior, but it is not a substitute for a documented compatibility guarantee:

    CREATE TABLE dbo.LedgerEntry
    (
        EntryId       bigint NOT NULL,
        PreviousId    bigint NULL,
        Amount        decimal(19,4) NOT NULL,
        CONSTRAINT PK_LedgerEntry PRIMARY KEY (EntryId),
        CONSTRAINT UQ_LedgerEntry_Previous UNIQUE (PreviousId),
        CONSTRAINT CK_LedgerEntry_Amount CHECK (Amount >= 0),
        CONSTRAINT FK_LedgerEntry_Previous
            FOREIGN KEY (PreviousId) REFERENCES dbo.LedgerEntry(EntryId)
    )
    WITH (LEDGER = ON (APPEND_ONLY = ON));
    

    Run this against the intended Azure SQL Database configuration and open a Microsoft support request for a ledger-specific contractual answer before making these constraints part of the audit security model.

    Dropping or changing constraints

    For an ordinary Azure SQL table, ALTER TABLE ... DROP CONSTRAINT removes a named constraint, and ALTER TABLE supports adding, dropping, enabling, and disabling constraints.^7^ Creating or modifying a foreign key requires ALTER permission on the table; creating a unique constraint likewise requires ALTER.^8^

    Therefore, a runtime principal granted only object-level SELECT and INSERT, with no ownership, role-derived DDL rights, ALTER, or broader implied permission, cannot use ALTER TABLE ... DROP CONSTRAINT. Check indirect grants and role membership as well: database CONTROL implies ALTER, and broader permissions can imply database-level rights.^9^

    Ledger immutability does not mean that all schema objects are indestructible. Ledger tables themselves can be dropped using soft-delete semantics: the table and dependent ledger objects remain physically represented for verification but are renamed and marked as dropped.^10^

    Concurrent conflicting inserts

    If the constraints are accepted and enforced on the ledger table, SQL transaction consistency requires completed transactions to satisfy integrity rules, and an unsuccessful transaction is rolled back.^11^ Thus two transactions cannot both commit duplicate values protected by an enforced primary key or unique constraint.

    For a foreign key, the outcome depends on the operations:

    • An insert whose referenced key does not exist cannot commit under an enforced FK.
    • Concurrent insertion of a parent and child is sensitive to transaction ordering and isolation; do not assume that an uncommitted parent in another transaction satisfies the child’s FK.
    • A self-referencing row can reference itself only if the engine’s statement-level FK validation accepts the row being inserted; this exact ledger case is not documented in the supplied sources.

    Applications should handle constraint failures and deadlocks as transaction failures rather than depend on one particular error code or which session loses.

    Primary versus readable-secondary reads

    Connections that require immediate cross-session visibility should use the primary:

    Server=tcp:<server>.database.windows.net;
    Database=<database>;
    ApplicationIntent=ReadWrite;
    Encrypt=True;
    

    ReadWrite is the default and routes to the read-write replica; ReadOnly routes to a readable replica when read scale-out applies. Read-only replicas are transactionally consistent within a session, but asynchronous relative to the primary and can lag without a fixed upper bound. Microsoft explicitly directs workloads requiring guaranteed cross-session consistency or immediate visibility to the primary.^12^

    Verify the current connection with:

    SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Updateability;
    

    For an absolute service-configuration guarantee regardless of connection-string intent, disable read scale-out. Otherwise, omit ApplicationIntent or set it explicitly to ReadWrite and reject a connection if the query returns READ_ONLY.^12^

    Database and logical-server deletion

    These are two different authorization planes:

    • T-SQL database deletion: In Azure SQL Database, only the provisioning server-level principal or members of the dbmanager role can execute DROP DATABASE.^13^
    • Azure SQL logical database management: Members of ##MS_DatabaseManager## can create and delete databases.^14^
    • Azure resource deletion: Azure RBAC roles are independent of SQL permissions. SQL DB Contributor can delete databases but cannot manage the parent logical server; SQL Server Contributor can manage SQL servers and databases.^15^ Resource owners and custom roles may also possess the relevant management-plane delete action.

    Deleting a logical server deletes its associated databases. If logical-server soft-delete retention is not configured, deletion is permanent with no server recovery option; server restore through soft delete is currently documented as preview.^16^

    Finally, Azure SQL Database point-in-time restore does not overwrite the source. It always creates a new database on the same logical server; the restored database can subsequently replace the original through rename/delete operations.^17^


    References

    1. Append-only ledger tables
    2. Primary and foreign key constraints
    3. Create unique constraints
    4. Unique constraints and check constraints
    5. ALTER TABLE table_constraint (Transact-SQL)
    6. Ledger considerations and limitations
    7. ALTER TABLE (Transact-SQL)
    8. Modify Foreign Key Relationships
    9. REVOKE Database Permissions (Transact-SQL)
    10. DROP TABLE (Transact-SQL)
    11. Transaction locking and row versioning guide
    12. Use read-only replicas to offload read-only query workloads
    13. DROP DATABASE (Transact-SQL)
    14. Azure SQL Database server roles for permission management
    15. Azure built-in roles for Databases
    16. Restore a deleted logical server in Azure SQL Database (preview)
    17. Restore a database from a backup in Azure SQL Database
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.