Adding a Non-Nullable Column to Existing Table Fails. Is The "Value" Attribute Being Ignored

Adding a non-nullable column to existing table fails. Is the value attribute being ignored?

Short answer

The "value" attribute will not work if you are adding a not-null constraint at the time of the column creation (this is not mentioned in the documentation). The SQL generated will not be able to execute.

Workaround

The workaround described in the question is the way to go. The resulting SQL will be:

1. Add the column

   ALTER TABLE layer ADD COLUMN abstract_trimmed varchar(455);
   

2. Set it to a non-null value for every row

   UPDATE table SET abstract_trimmed = 'No text';
   

3. Add the NOT NULL constraint

   ALTER TABLE layer ALTER COLUMN abstract_trimmed SET NOT NULL;
   

Why?

A column default is only inserted into the column with an INSERT. The "value" tag will do that for you, but after the column is added. Liquibase tries to add the column in one step, with the NOT NULL constraint in place:

ALTER TABLE layer ADD abstract_trimmed VARCHAR(455) NOT NULL;

... which is not possible when the table already contains rows. It just isn't smart enough.

Alternative solution

Since PostgreSQL 8.0 (so almost forever by now) an alternative would be to add the new column with a non-null DEFAULT:

ALTER TABLE layer
ADD COLUMN abstract_trimmed varchar(455) NOT NULL DEFAULT 'No text';

The manual:

When a column is added with ADD COLUMN and a non-volatile DEFAULT is
specified, the default is evaluated at the time of the statement and
the result stored in the table's metadata. That value will be used for
the column for all existing rows. If no DEFAULT is specified, NULL is
used. In neither case is a rewrite of the table required.

Adding a column with a volatile DEFAULT or changing the type of an
existing column will require the entire table and its indexes to be
rewritten. As an exception, when changing the type of an existing
column, if the USING clause does not change the column contents and
the old type is either binary coercible to the new type or an
unconstrained domain over the new type, a table rewrite is not needed;
but any indexes on the affected columns must still be rebuilt. Table
and/or index rebuilds may take a significant amount of time for a
large table; and will temporarily require as much as double the disk space.

Entity Framework Code first making a column non-nullable

That's because you allowed NULL values in that column, then tried to make it non-nullable. It will subsequently try to migrate your existing data into that newly non-nullable column, which will break because you already have NULL values in there.

Two solutions:

1) Change it back to nullable

2) Give it a default value for items that don't have a value.

How to add new column with default value from existing column in Liquibase

Since no one answered here I'm posting the way I handled it:

<changeSet id="Add MODIFY_USER_ID to ORDERS" author="Noam">
        <addColumn tableName="ORDERS">
            <column name="MODIFY_USER_ID" type="BIGINT">
                <constraints foreignKeyName="ORDERS_MODIFY_FK" referencedTableName="USERS" referencedColumnNames="ID"/>
            </column>
        </addColumn>
</changeSet>

<changeSet id="update the new MODIFY_USER_ID column to get the CREATOR" author="Noam">
    <sql>update ORDERS set MODIFY_USER_ID = CREATOR</sql>
</changeSet>

<changeSet id="Add not nullable constraint on MODIFY_USER_ID column" author="Noam">
    <addNotNullConstraint tableName="ORDERS" columnName="MODIFY_USER_ID" columnDataType="BIGINT"/>
</changeSet>

I've done this in three different change-sets as the documentation recommends

Why is SQL server throwing this error: Cannot insert the value NULL into column 'id'?

I'm assuming that id is supposed to be an incrementing value.

You need to set this, or else if you have a non-nullable column, with no default value, if you provide no value it will error.

To set up auto-increment in SQL Server Management Studio:

• Open your table in Design
• Select your column and go to Column Properties
• Under Indentity Specification, set (Is Identity)=Yes and Indentity Increment=1

Grails Database Migration plugin silently fails to add not null constraint

While at lunch I was thinking about this and wondered why I hadn't taken an obvious debugging step. The groovy script changes were made from scratch. The update to add the column worked, which means that the only difference between my DB and my domain object was the not nullable constraint:

class Game {
    String genre

    static constraints = {
        genre(nullable:  false, blank:  false)
    }
}

...so why not run grails dbm-gorm-diff and see what the plugin comes up with?

The result was this:

databaseChangeLog = {
    changeSet(author: "Ryan (generated)", id: "1340897310305-1") {
        addNotNullConstraint(columnDataType: "varchar(255)", columnName: "genre", tableName: "game")
    }
}

The only difference between my created change script and the script that the plugin made was the:

columnDataType: "varchar(255)"

I added that to the script I made (just to be sure I hadn't done anything else wrong) and bingo, update worked.

Update columns if input values are not null otherwise ignore and keep the existing values of column in database

You are inserting the user name straight into the SQL without escaping or even quoting. I think you simply missed the apostrophes.

To prevent SQL injection issues, NEVER insert SQL string constants from dynamic data, ALWAYS use PreparedStatement and insert markers.

Alternatively, escape the values, but using markers is much safer, and improves SQL performance by allowing the database to cache the compiled SQL statement.

String updateQuery = "UPDATE " + USER_TABLE +
                       " SET " + USER_TABLE_FIRST_NAME + "=IFNULL(? ," + USER_TABLE_FIRST_NAME + ")," +
                                 USER_TABLE_LAST_NAME + "=?," +
                                 USER_TABLE_ABOUT_ME + "=?," +
                                 USER_TABLE_CITY + "=?," +
                                 USER_TABLE_DOB + "=?" +
                     " WHERE " + USER_TABLE_ID + "=?";
PreparedStatement stmt = conn.prepareStatement(updateQuery);
stmt.setString(1, user.getFirstName());
stmt.setString(2, user.getLastName());
stmt.setString(3, user.getAboutMe());
stmt.setString(4, user.getCity());
stmt.setString(5, user.getDateOfBirth());
stmt.setString(6, user.getUserId());

Note: Answer extended to cover the null check issue.

When you're using simple string injection, "A='" + name + "'" becomes A='Joe' for a non-null value but A='null' for a null value, which is definitely not what you want.

By using parameter markers, the value of ? can be null, which means that IFNULL(?, Name) will give the exact behavior needed, i.e. using the value of ? when it's not null, and the value of NAME when ? is null.

---

Related Topics