Documentation

Editing a schema

How to edit columns, keys, indexes and triggers, write T-SQL for views and procedures, create objects and delete them.

Changes are made to your copy of the schema. Nothing reaches the database until you run a migration script yourself.

Editing a table

Select a table to open its editor below the diagram. It has six sections, each showing a count:

• Definition — the table's name and schema.
• Columns — add, delete, reorder and retype columns, and set nullability, identity and defaults.
• Primary key — choose which columns are in the key.
• Foreign keys — add and delete relationships, and set their referential actions.
• Indexes — add and delete indexes.
• Triggers — add and delete triggers, each edited as T-SQL.

Sections you open stay open as you move between tables. You can also add a relationship by dragging from one box to another on the diagram.

If the Indexes or Triggers section says they are unknown, the project was saved before they were read. Select Refresh in the project menu to load them.

Keys on more than one column

Tick PK against each column you want in the key. The Primary key section lists them in order, and lets you name the constraint.

A relationship must use every column of the key it points at. So a table pointing at a key of two columns needs both of them, and both pairs in the relationship:

1. Select the table that will point at the other.
2. Open Foreign keys and select Add foreign key.
3. Under References, choose the table it points at.
4. Pair the first column with the first column of the key.
5. Select + Column pair and pair the second, and so on.
6. Select Add.

The pairs are matched in order. The first pair is read against the first column of the key you are pointing at, the second against the second, and so on — not by name. Pair them in a different order and the relationship joins columns you did not mean, and SQL Server accepts it so long as the types line up. Open the other table's Primary key section to see the order the key is in, and follow it.

A relationship like this can only be built in the Foreign keys section. Dragging on the diagram joins one column to one column, so it cannot make a relationship on more than one column at all — it reports what the whole key is and sends you here.

One to one, or one to many

The Relationship list on a new foreign key offers two:

• One to many — many rows in this table may point at the same row in the other. This is the usual one, and what dragging on the diagram creates.
• One to one — at most one row in this table may point at any row in the other. A unique index is added on the referencing columns, which is what enforces it.

If the schema came from a source that does not report indexes, One to one says so on the spot and the relationship is created as one to many. Refresh the project against the database to read the indexes, then change it.

What happens to the rows underneath

On delete decides what SQL Server does to the rows pointing at a row that is deleted. On update does the same when the key's own values change. Both offer the same four:

• NoAction — the delete or update is refused while anything points at the row. The default, and the one to keep unless you have a reason.
• Cascade — the rows pointing at it are deleted too, or follow the new value. Deletes travel: a cascade from a table that is itself cascaded into removes rows several tables away.
• SetNull — the pointing columns are emptied and the rows stay. Every column in the relationship has to allow nulls.
• SetDefault — the pointing columns are set back to their default, which must then exist as a row in the other table.

The last two are checked by SQL Server when you run the script, not here: choose SetNull for a column that does not allow nulls and the script is refused when it runs.

Removing a relationship

In Foreign keys, select ✕ beside it. The line leaves the diagram, the relationship leaves this table, and the columns and their values stay exactly as they are — a foreign key is a rule about what may be written, not the data itself. The migration script drops the constraint and nothing else.

A relationship can also point at columns that are unique without being the key. In the Indexes section, select Add index, choose the columns and tick Unique; those columns can then be pointed at on their own.

Moving a key to another column

Untick PK, or tick it against a different column, and anything pointing at the old columns is affected: a relationship only works while the columns it points at are unique. WoodFireERD asks before changing anything, and offers two answers:

• Keep … unique — the columns stay unique under an index, and every relationship pointing at them survives. The key still moves.
• Let … repeat — the columns may hold the same value in several rows from then on, and the relationships pointing at them are removed. The columns and their values stay as they are.

After Let … repeat, draw the relationships again yourself once the rows carry values that match the new key. Nothing fills those in for you.

If the new key is a column you have just added, tick Let SQL Server number this column in the Primary key section. The server fills it with a different value for every row, and the migration script creates the key and the relationships without anything left to do by hand. Leave it unticked and the script adds the column, then tells you to fill it in before the key can be created.

Editing a view, function or procedure

Select it to open its T-SQL. Edit the script, then select Check script and update diagram to apply it. Until you do, your typing is a draft and the schema keeps the previous version.

The check reports:

• Syntax errors, with the line and column.
• SELECT * with no FROM.
• Tables and views named in the script that are not in your schema.
• A script that creates an object with a different name from the one you are editing.

Column names inside the script are not checked. A script can pass the check and still fail on a misspelled column when you run it.

Renaming one of these objects rewrites the CREATE line of its script to match.

Creating an object

1. Select + New under the filter box. It creates the kind of object shown on the open tab.
2. The object appears on the diagram straight away, marked with an amber dot in the list.
3. Select Keep to file it, or Cancel to remove it.

Until you select Keep, the object is not in your migration script. You can create several objects in a row, but changing object type, opening another object or redrawing the diagram asks you to keep or cancel them first.

Deleting

There is no undo anywhere in WoodFireERD.

• A table is deleted from its editor, not from the diagram. You are asked to confirm, and told which foreign keys the deletion removes. Its triggers go with it. Tables that point at it are not deleted.
• A relationship is deleted by clicking its line on the diagram and selecting Delete relationship. Both tables it joined stay on the diagram.
• A column, key, index or trigger is deleted from its section in the table editor.