Most developers who use ERWIN know it can generate SQL. Fewer than 20% use the forward engineering dialog to its full potential — choosing the right options, applying naming standards, previewing the output, and handling the common problems that cause generated DDL to fail on the first run.
This guide covers the full forward engineering workflow from start to clean, runnable SQL.
What is forward engineering?
Forward engineering is the process of generating SQL DDL (Data Definition Language — CREATE TABLE, CREATE INDEX, ALTER TABLE ADD CONSTRAINT, etc.) from your Physical Data Model. It's the "model to database" direction, as opposed to reverse engineering (database to model).
The output is a .sql file you can run against any instance of your target database to create the schema exactly as designed.
Prerequisites
- Your model is in Physical view (Model → Model Type → Physical)
- You've selected a target database (Model → Properties → Target Database)
- All entities have at least one PK defined
- You've run Validate Model (Tools → Validate Model) and resolved any critical errors
Step 1: Open the Forward Engineer dialog
Actions → Forward Engineer → Schema Generation Report
The dialog has three tabs: Options, Filter, and Preview/Generate.
Step 2: Configure Options tab
This is where most people skip important settings. Here's what to check/uncheck:
| Option | Check? | Why |
|---|---|---|
| Pre-Schema (DROP statements) | ⬜ Usually No | Only for full recreate — dangerous in production |
| Tables | ✅ Yes | Core output |
| Primary Keys | ✅ Yes | Always include |
| Foreign Keys | ✅ Yes — but separately | Generate after tables to avoid forward-ref errors |
| Indexes | ✅ Yes | Critical for performance |
| Defaults | ✅ Yes | DEFAULT NOW(), DEFAULT TRUE, etc. |
| Validation Rules (CHECK) | ✅ Yes | Status enums, range checks |
| Comments | ✅ Yes | Generates COMMENT ON — documentation in the DB |
| Triggers | ⬜ Usually No | Only if you've defined them in the model |
Generation Order — set this to: Tables → Indexes → Constraints (FK). This prevents errors where a FK references a table that hasn't been created yet.
Step 3: Apply naming standards first
Tools → Names → Apply Naming Standard
This converts your logical names ("Customer ID", "Order Date") into physical column names ("customer_id", "order_date") per your naming standard. If you skip this step, column names in the generated SQL will include spaces and capital letters, which requires quoting everywhere.
Step 4: Preview and save
In the Preview/Generate tab, click Preview first. Read through the output. Common things to check:
- All tables present? (Compare count to Model Explorer)
- Data types correct? (BIGINT not INT for PKs, NUMERIC not FLOAT for money)
- Foreign keys reference the right parent columns?
- Indexes on every FK column?
- No reserved words used as unquoted identifiers? (ORDER is reserved — use "order" or customer_order)
When satisfied, click Save to File. Name it with the database version: ecommerce_v1_pg15.sql.
Step 5: Run it against a test database
psql -h localhost -U postgres -d mydb -f ecommerce_v1_pg15.sql
Always run against dev/test first. Fix any errors (usually FK ordering or reserved word conflicts), then re-generate. Never run directly against production without reviewing the SQL.
Common errors and fixes
| Error | Cause | Fix |
|---|---|---|
| "relation does not exist" on FK | Parent table not yet created | Check generation order — Tables before Constraints |
| Syntax error near "order" | Reserved word used as table name | Rename to customer_order or quote: "order" |
| NOT NULL violation on existing data | Adding NOT NULL to a column with existing rows | Add default value first, then add constraint separately |
| Duplicate index name | ERWIN auto-naming collision | Apply naming standard first; rename conflicting indexes |