by Aaron

WebMatrix and SQL Server Compact 4.0 Table Editing workaround

Occasionally, I encounter the following error when editing a  SQL Server Compact 4.0 database table within WebMatrix.

Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.

image

The issue arises when adding a new column to an existing table, and specifying that the column should not allow Nulls (Allow Nulls = False).

The fix isn’t as obvious as I’d like, but it’s simple. In this case, the table does not have any data, which added further confusion. I understand that if I were to try to add a column to a table that had data, and the column was set to require a value, that it doesn’t make much sense (if the column is required, what value would be associated with the column for existing rows?).

However, the table is empty. There is a simple work around however.

  1. Set Allow Nulls to False.
  2. Put in a Default Value, appropriate for the Data Type specified.
  3. Save the table (CTRL+S)
  4. Remove the Default Value.
  5. Save the table (CTRL+S)

(I’m using WebMatrix until VS 2010 includes non-beta support for editing SQL Server Compact 4.0 databases)