Can my DB?

CREATE TABLE orders (
  id         INT PRIMARY KEY,
  customer_id INT NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• ON DELETE / ON UPDATE actions: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION (default).
• NO ACTION checks at statement end; RESTRICT raises immediately. DEFERRABLE defers the check to COMMIT.
• Multi-column FKs support MATCH FULL / MATCH PARTIAL.

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;

• InnoDB only. An index on the FK columns is required; MySQL creates one automatically if missing.
• ON DELETE / ON UPDATE: CASCADE, SET NULL, RESTRICT, NO ACTION. SET DEFAULT is accepted syntactically but partially supported.
• SET FOREIGN_KEY_CHECKS = 0 bypasses checks for the session; session-scoped, not transaction-scoped.

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;

• Same InnoDB-only story as MySQL. Required index, SET FOREIGN_KEY_CHECKS escape hatch, same action list.

CREATE TABLE orders (
  id          NUMBER PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
);

• ON DELETE CASCADE and SET NULL only — no ON UPDATE CASCADE.
• DEFERRABLE is available; RELY / NORELY controls whether the optimizer trusts the constraint for query rewrite without changing enforcement.

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• ON DELETE / ON UPDATE: CASCADE, SET NULL, SET DEFAULT, NO ACTION. SET DEFAULT needs a DEFAULT on the column.
• Cycles or deep chains of cascades aren't allowed; fall back to triggers.
• WITH NOCHECK skips validation of existing rows when adding an FK to a populated table.

-- Foreign key enforcement must be enabled per connection
PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• Off by default. PRAGMA foreign_keys = ON is per-connection and does not persist.
• Parent column must be a PRIMARY KEY or UNIQUE.
• ON DELETE / ON UPDATE: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.

-- Accepted syntactically but not enforced
CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT REFERENCES customers (id)
);

• Accepted and stored but not enforced. Redshift uses them as optimizer hints; integrity is the pipeline's job.

CREATE TABLE orders (
  id          INTEGER NOT NULL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

• ON DELETE / ON UPDATE: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION.
• Indexes on FK columns are not auto-created; build them for performance.
• SET INTEGRITY OFF / IMMEDIATE CHECKED suspends and re-validates constraints for bulk loads.

-- Accepted and visible in INFORMATION_SCHEMA but not enforced
CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT REFERENCES customers (id)
);

• Accepted and visible in INFORMATION_SCHEMA.TABLE_CONSTRAINTS but not enforced; optimizer-only.

-- FK constraints are declarable but NOT ENFORCED
CREATE TABLE orders (
  id          INT64 NOT NULL,
  customer_id INT64 NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    NOT ENFORCED
);

• The NOT ENFORCED qualifier is required; BigQuery rejects FK syntax without it.
• Declared FKs drive join elimination and other rewrites in the optimizer.