# No-FK, semantic-FK reality

The xly schema has **zero triggers** and **zero foreign-key constraints
on any xly-authored table** (the `gds*`, `ele*`, `mft*`, `quo*`, `sal*`,
`acc*`, … families). The only FK constraints that exist sit on bundled
third-party schemas — Activiti's `act_*` tables and Quartz's `qrtz_*`
tables — neither of which the framework's own runtime joins through.
Every join the framework relies on for its own metadata or business
data is by convention only.

This is a deliberate design choice and an important one to understand
before reading any further.

## Why xly disabled FKs

Two reasons the architecture gives:

1. **Bulk-write performance.** Mass inserts (work-order calculation,
   month-end closures, batch imports) write hundreds of thousands of
   rows at a time. With FKs enabled, MySQL validates each row's
   references on insert — for the volumes xly handles, this is the
   limiting factor.
2. **Schema-migration agility.** xly evolves quickly: new modules,
   new fields, new tables. With FKs, every schema change has to consider
   the constraint graph; without them, a `CREATE TABLE` or `ALTER TABLE`
   is a local operation.

Both are real considerations, but neither is a slam-dunk argument for
"zero FKs across the entire schema":

- **Bulk-write performance** can be addressed surgically: disable
  constraints during the batch (`SET FOREIGN_KEY_CHECKS = 0`),
  re-enable after, validate. xly's choice was instead to not have
  FKs at all, which means *every* read also pays the cost of trusting
  ad-hoc proc validation rather than DB-enforced integrity.
- **Schema-migration agility** is improved by no-FKs, but at the
  price of moving every referential check into application code (or
  forgetting it). In practice this means the integrity work an FK
  would do automatically is now duplicated across hundreds of stored
  procedures, with no compile-time guarantee any given proc actually
  does the check (see Failure modes below).

A more honest framing: the system traded **DB-enforced integrity**
for **operational convenience at write time and DDL time**. The
bug surface that trade introduced (orphan rows, cross-tenant
references that go undetected, integrity bugs surfacing weeks
later) is the cost paid every day the system runs.

## What a "semantic FK" is

A **semantic FK** is a column that *would be* a foreign key if FKs were
enabled, but isn't. The relationship is encoded in:

- **Column naming.** A `sCustomerId` column is *understood* to reference
  `eleCustomer.sId`. A `sParentId` on a slave table is *understood* to
  reference the master's `sId`.
- **Co-mention in stored procedures and MyBatis mappers.** The runtime
  joins `A.sParentId = B.sId` in many places; reading the proc body
  reveals which tables are paired.
- **Documentation in column comments.** `COLUMN_COMMENT` is filled in
  for many columns and often names the referenced concept (e.g., `加工商ID`).
- **Metadata declarations.** The framework's `gdsconfigformslave.sActiveId`
  / `sActiveKey` columns explicitly name the *form* and *field* a
  dropdown-control points at — a metadata-encoded FK.

None of these are enforced. A row can be deleted from `eleCustomer` and
nothing in the database will stop you; orphan `sCustomerId` references
will simply hang around.

## Recovering relationships

When the auto-catalog or wiki needs to know "what does column X reference",
the lookup process is:

1. Read the column comment (often the most helpful single line).
2. Search the schema for matching ID columns (`grep` `recon/columns.tsv`
   for `XSomethingId`).
3. Search the codebase for SQL fragments that JOIN on the column.
4. Check `gdsconfigformslave.sActiveId` — the framework records dropdowns
   and lookups there.
5. As a last resort, follow the master/slave naming convention.

This is the work the **auto-catalog** is meant to streamline: each
generated table page lists the table's columns and (in a future
enhancement) the procs that reference them, narrowing the search.

## Failure modes

The cost of disabling FKs lives at runtime. Three failure modes recur:

- **Orphan rows.** A `sCustomerId` whose customer was deleted. The
  framework's queries left-join through these and either silently drop
  them or display blanks.
- **Mismatched IDs across tenants.** Without an FK, nothing prevents a
  row from referencing an ID owned by a different `sBrandsId`. The
  multi-tenant filter (Slice 2) is the only thing keeping this from
  exploding into cross-tenant data leakage.
- **Procs that need to manually validate referential integrity.** Many
  of xly's stored procedures explicitly check `EXISTS (…)` before
  insert. The integrity work that an FK would do automatically is now
  scattered across the proc body. Skip it once and the bug surfaces
  weeks later.

The wiki's job is to surface these relationships even though the
database does not. Most of that surfacing happens in the auto-catalog
and in slice cross-references; this page is the canonical "why aren't
there FKs and what should I trust instead" reference.