Designing tables, keys and constraints

In the first week of October 2026, Hana Sato started rebuilding the database behind Finch Rewards, the loyalty program of Harrow & Finch Home Supply in Des Moines. The old program ran on a twelve-year-old SQL Server database that grew one column at a time, mostly in a hurry before a promotion. It has a members table with 94 columns, three different ways of storing a phone number and no foreign keys at all. The new Finch Rewards API will run on Azure SQL Database, and Hana wants the schema to be something the next developer can trust. Elena Brandt, the database administrator who runs the DBA clinic in MSAZ-280 and MSAZ-281, agreed to meet her twice a week in October and early November for what they called design sessions. Liam Ferreira, the junior developer who maintains StockLine, joined them because the same habits will matter when StockLine moves to Managed Instance in 2027.

Hana is preparing for exam DP-800, Developing AI-Enabled Database Solutions, which leads to the Microsoft Certified: SQL AI Developer Associate certification. The exam's largest skill group, weighted 35 to 40 in 100 in the March 2026 outline, is designing and developing database solutions: tables, specialized tables, JSON, constraints, sequences, partitioning, programmability objects, advanced T-SQL and AI-assisted tools. A second group, also weighted 35 to 40, includes the database projects, source control and pipelines covered later in this course. Security, monitoring and tuning are in MSAZ-281, and vectors, embeddings and retrieval are in MSAZ-301. This course follows the design sessions from the first table to a finished development case study.

Start from the business rules

Elena's first question was not about data types. "Tell me the rules," she said, "and we'll make the database enforce them." Hana listed what the loyalty program promises. Every member has one account and one email address that nobody else uses. A member earns points on purchases at one of the 23 stores or online, and every points change has a reason. A member's balance can never go below zero. A member belongs to one tier, Sparrow, Finch or Kestrel, based on points earned in the last twelve months. Offers have a start date and an end date, and the end comes after the start. A redemption uses points from one member against one offer.

Each of those sentences becomes a table, a key or a constraint. Rules written only in application code are enforced only by the code that remembers them, and the old database proves the point: the nightly import job, a support tool and two marketing scripts all write to the members table, and each enforces a slightly different version of the rules. Constraints in the database apply to every writer, including the ones nobody remembers. They also tell the query optimizer things it can use. A trusted foreign key lets the optimizer remove a join it does not need, and a check constraint can let it skip a partition or a branch of a plan.

Tables and keys

A primary key identifies each row and cannot be null. In SQL Server and Azure SQL, a primary key creates a unique index, clustered by default unless a clustered index already exists. For most tables in a transactional system, Elena prefers a narrow, ever-increasing clustered key such as an int or bigint, because it keeps the clustered index compact, makes inserts append at the end and gives every nonclustered index a short row locator. A natural key, such as the member's email address, still matters, but it is better enforced with a unique constraint than used as the clustered key, because email addresses change and are long.

Hana asked whether she should use uniqueidentifier keys, since the mobile app creates some records before it is online. Elena's answer was that a random GUID as a clustered key scatters inserts across the whole index and causes page splits and fragmentation. If the app needs to generate identifiers, a GUID can be an alternate key with a unique constraint, while the clustered key stays an identity or sequence value. NEWSEQUENTIALID() produces increasing GUIDs on the server, but values created in the app cannot use it. Finch Rewards will use a bigint identity for MemberId and a uniqueidentifier column, PublicId, that the API exposes to the app, so internal numbers never appear in URLs.

CREATE TABLE rewards.Member
(
    MemberId      bigint IDENTITY(1,1) NOT NULL,
    PublicId      uniqueidentifier NOT NULL
                  CONSTRAINT DF_Member_PublicId DEFAULT NEWID(),
    Email         nvarchar(254) NOT NULL,
    GivenName     nvarchar(100) NOT NULL,
    FamilyName    nvarchar(100) NOT NULL,
    HomeStoreId   int NULL,
    TierCode      char(1) NOT NULL
                  CONSTRAINT DF_Member_Tier DEFAULT 'S',
    PointsBalance int NOT NULL
                  CONSTRAINT DF_Member_Points DEFAULT 0,
    JoinedOn      date NOT NULL
                  CONSTRAINT DF_Member_Joined DEFAULT CAST(SYSUTCDATETIME() AS date),
    CONSTRAINT PK_Member PRIMARY KEY CLUSTERED (MemberId),
    CONSTRAINT UQ_Member_PublicId UNIQUE (PublicId),
    CONSTRAINT UQ_Member_Email UNIQUE (Email),
    CONSTRAINT CK_Member_Tier CHECK (TierCode IN ('S', 'F', 'K')),
    CONSTRAINT CK_Member_Points CHECK (PointsBalance >= 0),
    CONSTRAINT FK_Member_Store FOREIGN KEY (HomeStoreId)
        REFERENCES rewards.Store (StoreId)
);

Naming every constraint was Elena's first habit to pass on. If a constraint has no name, SQL Server generates one such as PK__Member__0CF04B18A1B2C3D4, which differs in every database where the script runs. Comparison tools then report a difference that is not real, and a deployment script that tries to drop the constraint by name fails in one environment and works in another. Named constraints also make error messages useful: a violation of CK_Member_Points tells the support team exactly which rule the caller broke.

The five kinds of constraint

DP-800 names five constraint types: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and DEFAULT. NOT NULL is technically a column property rather than a constraint, but it does the most work of all. Every column that the business says must have a value should be NOT NULL, because a null in a required column is a bug that has already happened. Hana made 31 of the 38 columns in her first design NOT NULL, and each of the other seven has a sentence in the design notes explaining what a null means for it.

A unique constraint in SQL Server treats null as a value, so a column with a unique constraint can hold only one null. That surprises developers who come from other database engines, where many nulls are allowed. Finch Rewards members can add a phone number, and two members without one must both be allowed. Hana used a filtered unique index instead of a unique constraint: CREATE UNIQUE INDEX UX_Member_Phone ON rewards.Member (Phone) WHERE Phone IS NOT NULL. The filter leaves nulls out of the index, so any number of members can have no phone, while two members still cannot share one.

A check constraint can compare columns in the same row, such as an offer's end date with its start date, but it cannot look at other rows or other tables. Hana's rule that the points balance cannot fall below zero works as a check because the balance is a column of the row. The rule that a member's tier matches points earned in the last twelve months cannot be a check constraint, because it depends on many rows in the points table. That rule belongs in the stored procedure that recalculates tiers, covered in lesson 4. Elena warned against the workaround of calling a scalar function from a check constraint to read other rows: it runs for every row, is not checked when the other table changes, and can be bypassed in ways that make the rule look enforced when it is not.

Foreign keys and what happens on delete

A foreign key says that a value must exist in the referenced table's primary key or unique key. By default, deleting a referenced row that still has dependants fails. ON DELETE CASCADE deletes the dependants too, ON DELETE SET NULL clears the reference, and ON DELETE SET DEFAULT sets it to the column default. Hana's first draft cascaded deletes from Member to PointsTransaction. Elena asked what would happen when a support agent deleted a test account that turned out to be real, and Hana changed it. Points history is financial history; it should not disappear because a member row did. When a member closes an account, the API will mark the member closed and keep the history for the retention period the privacy officer, Carmen Ortiz, sets.

Every foreign key column should normally have an index. SQL Server does not create one automatically. Without it, deleting or updating a row in the parent table makes the engine scan the child table to check for dependants, and joins from parent to child cannot seek. Hana's PointsTransaction table will hold about 30 million rows after migration, so a delete of one member without an index on MemberId would read all of them. The index also serves the most common query in the API, which lists one member's recent transactions.

CREATE TABLE rewards.PointsTransaction
(
    TxnId        bigint IDENTITY(1,1) NOT NULL,
    MemberId     bigint NOT NULL,
    StoreId      int NULL,          -- null for online purchases
    ReasonCode   varchar(12) NOT NULL,
    Points       int NOT NULL,
    PostedAtUtc  datetime2(3) NOT NULL
                 CONSTRAINT DF_Txn_Posted DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_PointsTransaction PRIMARY KEY CLUSTERED (TxnId),
    CONSTRAINT FK_Txn_Member FOREIGN KEY (MemberId) REFERENCES rewards.Member (MemberId),
    CONSTRAINT FK_Txn_Store FOREIGN KEY (StoreId) REFERENCES rewards.Store (StoreId),
    CONSTRAINT CK_Txn_Points CHECK (Points <> 0),
    CONSTRAINT CK_Txn_Reason CHECK (ReasonCode IN
        ('PURCHASE', 'RETURN', 'BONUS', 'REDEEM', 'EXPIRE', 'ADJUST'))
);
CREATE INDEX IX_Txn_Member_Posted
    ON rewards.PointsTransaction (MemberId, PostedAtUtc DESC)
    INCLUDE (Points, ReasonCode);

Trusted and untrusted constraints

A foreign key or check constraint can be added WITH NOCHECK, which skips checking the rows already in the table, or disabled with ALTER TABLE ... NOCHECK CONSTRAINT during a bulk load. Either way the constraint becomes untrusted. New rows are checked once it is enabled again, but the optimizer can no longer rely on it, because existing rows might break the rule. The is_not_trusted column in sys.foreign_keys and sys.check_constraints shows the state. Re-enabling with ALTER TABLE ... WITH CHECK CHECK CONSTRAINT checks every existing row and makes the constraint trusted again. Elena's migration plan says every constraint disabled for the initial load is re-enabled WITH CHECK before the API goes live, and the release checklist queries is_not_trusted to prove it.

SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS schema_name,
       OBJECT_NAME(parent_object_id) AS table_name,
       name AS constraint_name, type_desc, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE is_not_trusted = 1 OR is_disabled = 1
UNION ALL
SELECT OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id),
       name, type_desc, is_disabled, is_not_trusted
FROM sys.check_constraints
WHERE is_not_trusted = 1 OR is_disabled = 1;

Sequences

An identity column generates numbers for one table. A sequence is a separate object that generates numbers on request, so several tables can share one series, the application can get a number before inserting, and the numbers can cycle or step by more than one. Finch Rewards prints a member card number on new plastic cards, and Harrow & Finch wants the card numbers to be unique across members and the gift cards that a separate table holds. Hana created one sequence, rewards.CardNumber, starting at 600000001, and both tables take their card numbers from it with NEXT VALUE FOR in a default constraint.

Two properties of sequences catch people out. First, numbers are not transactional: a value taken inside a transaction that rolls back is gone, so gaps are normal and cannot be prevented. Identity columns have the same behavior. A rule that says card numbers must have no gaps cannot be met with either, and should be challenged rather than implemented. Second, the CACHE option keeps a block of numbers in memory for speed, and an unexpected restart can lose the unused part of the block. For card numbers that is acceptable; for invoice numbers that a tax authority checks, it is not, and those need a different design.

CREATE SEQUENCE rewards.CardNumber AS bigint
    START WITH 600000001 INCREMENT BY 1
    MINVALUE 600000001 MAXVALUE 699999999
    NO CYCLE CACHE 50;

ALTER TABLE rewards.Member ADD
    CardNumber bigint NOT NULL
        CONSTRAINT DF_Member_Card DEFAULT (NEXT VALUE FOR rewards.CardNumber),
    CONSTRAINT UQ_Member_Card UNIQUE (CardNumber);

Partitioning, briefly

The DP-800 outline also lists partitioning for tables and indexes. A partition function maps values of a column, usually a date, to numbered partitions, and a partition scheme maps those partitions to filegroups; in Azure SQL Database every partition goes on the PRIMARY filegroup, but partitioning still works. Partitioning does not usually make queries faster on its own. Its main value is management: an old month can be switched out of the table in an instant, or truncated by partition, instead of being deleted row by row. Hana considered partitioning PointsTransaction by month and decided against it for now. At 30 million rows growing by about 1.1 million a month, the table is well within what a good clustered index handles, and points expire by a business rule rather than by age. Elena agreed and wrote one line in the design notes: revisit when the table passes 200 million rows or when a retention rule requires dropping whole months.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE Store (StoreId INTEGER PRIMARY KEY, Name TEXT NOT NULL);
CREATE TABLE Member (
  MemberId INTEGER PRIMARY KEY,
  Email TEXT NOT NULL UNIQUE,
  TierCode TEXT NOT NULL DEFAULT 'S' CHECK (TierCode IN ('S','F','K')),
  PointsBalance INTEGER NOT NULL DEFAULT 0 CHECK (PointsBalance >= 0),
  HomeStoreId INTEGER REFERENCES Store(StoreId));
INSERT INTO Store VALUES (4, 'Store 4');
""")
tests = [
  ("valid member", "INSERT INTO Member (Email, HomeStoreId) VALUES ('a@example.com', 4)"),
  ("duplicate email", "INSERT INTO Member (Email) VALUES ('a@example.com')"),
  ("negative balance", "INSERT INTO Member (Email, PointsBalance) VALUES ('b@example.com', -50)"),
  ("unknown tier", "INSERT INTO Member (Email, TierCode) VALUES ('c@example.com', 'X')"),
  ("missing store", "INSERT INTO Member (Email, HomeStoreId) VALUES ('d@example.com', 99)"),
]
for label, sql in tests:
    try:
        db.execute(sql)
        print(f"{label:17s} accepted")
    except sqlite3.IntegrityError as e:
        print(f"{label:17s} rejected: {e}")
print("rows in Member:", db.execute("SELECT COUNT(*) FROM Member").fetchone()[0])

valid member      accepted
duplicate email   rejected: UNIQUE constraint failed: Member.Email
negative balance  rejected: CHECK constraint failed: PointsBalance >= 0
unknown tier      rejected: CHECK constraint failed: TierCode IN ('S','F','K')
missing store     rejected: FOREIGN KEY constraint failed
rows in Member: 1