How DrawDB Implements SQL Constraint Handling: NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT

DrawDB implements SQL constraint handling through a centralized Constraint enum in src/data/constants.js that standardizes referential actions across DBML import, export, and SQL generation pipelines.

DrawDB is an open-source database diagramming tool that supports the full suite of SQL referential integrity actions. Understanding how DrawDB handles constraint handling reveals a clean architecture that maps standard SQL keywords to internal constants, ensuring consistent behavior when parsing DBML files, exporting schemas, and generating migration scripts.

Defining Constraint Constants in constants.js

The foundation of DrawDB's constraint handling resides in src/data/constants.js. This file exports a Constraint object that defines human-readable constants for all five SQL referential actions:

// src/data/constants.js
export const Constraint = {
  NONE: "No action",
  RESTRICT: "Restrict",
  CASCADE: "Cascade",
  SET_NULL: "Set null",
  SET_DEFAULT: "Set default",
};

These values serve as the canonical representation throughout the application. When the diagram model stores foreign key behavior, it references these constants rather than raw strings, ensuring type safety and consistent terminology across the codebase.

Parsing DBML Referential Actions in parse.js

When importing DBML files, DrawDB converts textual constraint keywords into the internal Constraint enum values. The parsing logic lives in src/utils/dbml/parse.js.

Mapping Keywords to Constants

The parser maintains a CONSTRAINT_BY_KEYWORD lookup table that maps lowercase DBML keywords to their corresponding Constraint values:

// src/utils/dbml/parse.js
const CONSTRAINT_BY_KEYWORD = {
  "no action": Constraint.NONE,
  restrict: Constraint.RESTRICT,
  cascade: Constraint.CASCADE,
  "set null": Constraint.SET_NULL,
  "set default": Constraint.SET_DEFAULT,
};

function constraintFor(keyword) {
  return (
    CONSTRAINT_BY_KEYWORD[String(keyword).toLowerCase()] ?? Constraint.NONE
  );
}

The constraintFor function normalizes input by converting keywords to lowercase and defaults to Constraint.NONE when encountering unrecognized values.

Processing Referential Actions

During the parsing of a Ref declaration, the parseRef function extracts onDelete and onUpdate clauses and stores them using the internal enum:

// src/utils/dbml/parse.js
function parseRef(ref) {
  // ... other parsing logic ...
  return {
    // ... other properties ...
    deleteConstraint: constraintFor(ref.onDelete),
    updateConstraint: constraintFor(ref.onUpdate),
  };
}

This ensures that deleteConstraint and updateConstraint properties in the diagram model always contain one of the five valid Constraint values.

Exporting Constraints to DBML in dbml.js

When exporting diagrams back to DBML format, DrawDB reverses the mapping process. The export utility in src/utils/exportAs/dbml.js converts internal constants back to DBML syntax.

The constraintKeyword helper lowercases the stored constant for DBML compatibility:

// src/utils/exportAs/dbml.js
function constraintKeyword(constraint) {
  return String(constraint ?? Constraint.NONE).toLowerCase();
}

The refBlock function then injects these values into the DBML Ref block syntax:

// src/utils/exportAs/dbml.js
function refBlock(rel, tables) {
  // ... relationship resolution logic ...
  const settings = `[ delete: ${constraintKeyword(rel.deleteConstraint)},
                     update: ${constraintKeyword(rel.updateConstraint)} ]`;
  return `Ref ${name}{\n\t${columnRef(startTable.name, startFields)} ${symbol}
          ${columnRef(endTable.name, endFields)} ${settings}\n}`;
}

This produces standard DBML output like [ delete: cascade, update: set null ], maintaining fidelity between the visual diagram and textual representation.

SQL Generation from Stored Constraints

Beyond DBML serialization, the stored constraint values drive SQL DDL generation. In src/utils/migrations/diffToSQL.js, DrawDB consumes the deleteConstraint and updateConstraint properties to generate database-specific ON DELETE and ON UPDATE clauses for PostgreSQL, MySQL, SQLite, and other supported engines. This ensures that the visual diagram, DBML representation, and generated SQL remain synchronized.

Practical Implementation Examples

Importing DBML with Cascade and Restrict Rules

Consider a DBML file defining a foreign key with specific referential actions:

Table users {
  id int [pk, increment]
  name varchar
}

Table posts {
  id int [pk, increment]
  user_id int
  title varchar
}

Ref fk_posts_user {
  posts.user_id > users.id [ delete: cascade, update: restrict ]
}

After parsing through src/utils/dbml/parse.js, the resulting diagram model contains:

{
  tables: [...],
  relationships: [
    {
      name: "fk_posts_user",
      startTableName: "posts",
      startFieldNames: ["user_id"],
      endTableName: "users",
      endFieldNames: ["id"],
      deleteConstraint: "Cascade",        // Constraint.CASCADE
      updateConstraint: "Restrict",      // Constraint.RESTRICT
      cardinality: "many_to_one",
    },
  ],
}

Exporting Diagram Models to DBML

When exporting a diagram programmatically using the DBML export utility:

import { toDBML } from "./utils/exportAs/dbml";

const diagram = {
  database: "postgresql",
  tables: [/* ... */],
  relationships: [
    {
      name: "fk_posts_user",
      startTableId: 2,
      endTableId: 1,
      deleteConstraint: "Cascade",
      updateConstraint: "Restrict",
      cardinality: "many_to_one",
    },
  ],
};

const dbml = toDBML(diagram);
console.log(dbml);

The output includes the constraint block with proper formatting:

Ref fk_posts_user {
  posts.user_id > users.id [ delete: cascade, update: restrict ]
}

Summary

• Centralized Constants: The Constraint enum in src/data/constants.js defines the five SQL referential actions (NONE, RESTRICT, CASCADE, SET_NULL, SET_DEFAULT) used throughout DrawDB.
• Bidirectional Mapping: src/utils/dbml/parse.js maps incoming DBML keywords to internal constants via constraintFor, while src/utils/exportAs/dbml.js converts them back using constraintKeyword.
• Model Integration: Relationship objects store constraints as deleteConstraint and updateConstraint properties, ensuring consistent internal representation regardless of import source.
• SQL Synchronization: src/utils/migrations/diffToSQL.js consumes these values to generate accurate ON DELETE and ON UPDATE clauses for multiple database dialects.

Frequently Asked Questions

What SQL constraint actions does DrawDB support?

DrawDB supports all five standard SQL referential actions: NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT. These are defined as constants in src/data/constants.js and recognized during DBML import regardless of case formatting.

How does DrawDB map DBML keywords to internal values?

During parsing in src/utils/dbml/parse.js, the constraintFor function uses a CONSTRAINT_BY_KEYWORD lookup table to convert lowercase DBML keywords (like "cascade" or "set null") into the corresponding Constraint enum values. Unrecognized keywords default to Constraint.NONE.

Where are constraint values stored in the diagram model?

Constraint values are stored as string properties on relationship objects within the diagram model. Specifically, each relationship contains deleteConstraint and updateConstraint fields that hold values like "Cascade" or "Set null" as defined in the Constraint enum.

How do constraints translate to SQL DDL statements?

When generating SQL migrations, src/utils/migrations/diffToSQL.js reads the deleteConstraint and updateConstraint values from relationship objects and maps them to database-specific ON DELETE and ON UPDATE syntax. This ensures that foreign key constraints defined visually in DrawDB produce valid SQL for PostgreSQL, MySQL, SQLite, and other supported databases.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}