Constraints & Declarative Integrity

Status: Reviewed (deep-dive v1)
Stack note: SPIKE keys (AlertType+EntityKey, InstanceKey) need constraints, not “we’ll be careful in the proc.” Protects merges (SQL-19) and Blazor from orphans.
Goal: PK/UQ/FK/CHECK/filtered UQ habits for the warehouse.

Quick chooser

Need	Constraint
Natural key uniqueness	`PRIMARY KEY` or `UNIQUE`
One active alert per key	Filtered UQ `WHERE Status IN ('Open','Acked')`
Child rows	`FOREIGN KEY` to parent
Allowed status/severity	`CHECK` or FK to lookup table
Soft rules in UI only	Not enough — enforce in DB

Patterns

-- lookup + FK
CREATE TABLE dbo.AlertSeverity (
  SeverityCode varchar(16) NOT NULL PRIMARY KEY,
  SortOrder int NOT NULL
);

ALTER TABLE dbo.Alert ADD
  CONSTRAINT FK_Alert_Severity FOREIGN KEY (SeverityCode)
    REFERENCES dbo.AlertSeverity(SeverityCode);

ALTER TABLE dbo.Alert ADD
  CONSTRAINT CK_Alert_Status
  CHECK (Status IN (N'Open', N'Acked', N'Closed'));

-- SPIKE-07 style
CREATE UNIQUE INDEX UX_Alert_Active
  ON dbo.Alert(AlertType, EntityKey)
  WHERE Status IN (N'Open', N'Acked');

Habits

[ ] Name constraints (FK_, CK_, UQ_)
[ ] Merges assume UQ — don’t rely on SELECT DISTINCT forever
[ ] FKs: ON DELETE explicit (usually NO ACTION for alerts)
[ ] Deploy: create lookups before fact FKs
[ ] NOCHECK only as temporary repair with a ticket

Done when

[ ] Hot warehouse tables have documented keys + FKs/CHs
[ ] Active-alert filtered UQ in place if using evaluate pattern
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-27 Constraints & Declarative Integrity v1