Static table columns

The Columns step defines the fields of a Static table: their names, data types, key and flags, default values and the groups that can read them. It is the second step of the wizard, after General, and the table needs at least one of your own columns marked PK before it can be deployed.

The columns grid

The grid lists every column as a row. These are the cells you work with:

• Every static table starts with a locked key row named after the table: Countries Key for a table called Countries. You cannot rename or remove it, and you do not need to: your key is a column you mark PK yourself.
• The toolbar holds Add new column and Delete selected columns; the delete stays greyed out until you select a row.
• Drag a row to reorder. Only saved rows move; a row you added but have not saved stays at the end until the step is saved.
• A row that cannot be changed carries a lock icon. Hover it to see the reason.
• The grid does not store view tweaks such as hidden or resized columns, and a table with no columns yet shows No columns defined.

For the rules shared by every Warehouse table, including locked cells and the full validation set, see The table columns grid and Define table columns. Data domains and PII have their own reference: Column data domains, defaults and PII.

Add and name a column

1. Click Add new column. A row named New Column 1 appears, using the next free number.
2. Click the name to edit it. A generated name clears itself so you can type straight away, and an empty cell shows Enter name....
3. Press Enter or click away to keep the new name. Press Escape to drop the edit while the cell is still open.
4. Watch for the red mark: a duplicate name (capital letters are ignored) or a name the system reserves fails the check, and hovering the mark shows Column name must be unique or This is a system reserved name.

Set the key and the flags

Four flag cells sit in the grid. Hover a header to see its full name.

• A Static table needs a key. Mark the column or columns that identify each row as PK; a save without one is refused with At least one user-defined column must be marked as a Primary Key., and the table cannot deploy without a key.
• PK is offered on your own columns. A row that holds a foreign key cannot take PK or M, and a row with a computed script cannot take PK, FK or M.
• FK holds a reference to another table. Foreign key values are left out of export files.

Set a default value

• Default Value is used when no value is given for the column. The cell's tooltip states the rule: "String/text defaults must be wrapped in single quotes, e.g. 'N/A'. Numbers and dates take raw values, and SQL expressions like now64(3) are allowed."
• For a text column, the grid shows the value with quotes while it stores the text itself.

Describe a column

• Click the message icon next to a column name to describe it. The popover is headed Column description.
• Type the description in "Add description for this column...", then click Save. Clear removes an existing description, and an empty one reads No description provided.
• The icon appears on your own unlocked columns; it stays highlighted once a column has a description.

Give a column its permissions

• The Permission Group cell picks the groups that can read this column. Click it to choose groups; the field shows Add groups… and its search reads Search groups….
• The options come from the table's Permission Groups on General. When the table has none, the cell shows No permission groups added to table with an Add in General shortcut; a search that finds nothing shows No matches found.
• A column takes up to 100 permission groups, and a new column starts with the table's groups already chosen.
• You do not add the admin group: it always has full access to all columns.
• Permissions and row-level security are covered in Warehouse permissions and row-level security.

Save the step

• Apply saves everything you changed on the step in one go and appears in the footer while there are changes. The confirmation reads Column changes saved successfully. on the first save and Column changes updated successfully. afterwards.
• If a check fails, a Validation Error toast names the problem and the step does not save. If the save fails, it reports Failed to save columns. and keeps your changes.
• A column saved with a dot in its name changes the confirmation: the toast reads Successfully saved with a note that the dotted name is tricky and usually better avoided.
• Revert discards the changes made since the last save and confirms with Changes Reverted.
• The rows themselves come later: see Static table data mapping.

Questions, answered

Why is there a locked column named after my table?

Every static table starts with a generated key column named after the table, for example Countries Key. It is locked: you cannot rename or remove it. It is not the key you choose. Mark your own column or columns as PK for the table's primary key.

What do PK, FK, M and CC mean?

They are the four flags: PK Primary Key (whether this column is part of the primary key), FK Foreign Key (a reference to another table), M Mandatory (whether this column is required) and CC Computed Column (a script for calculated values). Hover a flag's header to see its full name.

How do I add a description to a column?

Click the message icon next to the column name, type into Column description and click Save. Clear removes an existing description. The icon shows on your own unlocked columns, and an empty description reads No description provided.

How do I write a default value for a column?

Wrap text values in single quotes, for example 'N/A'. Numbers and dates take raw values, and SQL expressions such as now64(3) are allowed. A text default is shown quoted in the grid while the text itself is stored.

What happens when I set a computed script on a column?

The column becomes a computed column, with its value produced by the script. Adding a script clears the row's FK, M and Default Value, those cells stay unavailable while the script exists, and the row cannot be marked PK.

What happens if a column name contains a dot?

A dot is allowed, but risky: SQL reads a name such as a.b as a table and column pair. When you commit the name, the warning Dot in column name appears with the text "Dots are risky in column names - Consider renaming. SQL reads them as table.column." The warning is advisory and never blocks the save. If the dotted name is saved, the confirmation carries the note that it is tricky and usually better avoided, and the saved name can no longer be renamed back.

How do I undo a column change I just made?

While a name is still open for editing, press Escape to drop it. For anything already committed to the grid, click Revert in the footer: it drops every change made since the last save and reports Changes Reverted and "All changes in the columns tab have been reset." After a save, edit the cell again to set the value back.

Is there a limit on how many permission groups a column can have?

Yes, a column takes up to 100 permission groups. You do not add the admin group: it always has full access to all columns.