Phinx: A column can not be "NOT NULL" unless it has a default value (SQLite)

[nixxquality]

In the SQLite adapter you can not use just array('null' => false) to specify that a column is "NOT NULL", you also have to specify a default value[1].

$def .= ($column->isNull() || is_null($default)) ? ' NULL' : ' NOT NULL';

This behavior is different from the other adapters[2][3], so I am assuming it is a bug.

$def .= ($column->isNull() == false) ? ' NOT NULL' : ' NULL';
$buffer[] = $column->isNull() ? 'NULL' : 'NOT NULL';

[shadowhand]

Can you create a pull request for this please? It appears we don't have a test case for this.

[robmorgan]

I've merged a test which is reproducing this behaviour, but I've marked it as skipped for now.

I tried to fix this bug but it turns out its not as simple as it seems. I can make it work when creating a table, however altering one is a different story. SQLite only supports a limited subset of ALTER TABLE:

The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:

• If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

See: http://www.sqlite.org/lang_altertable.html for more information.

I guess Phinx would have to work around this behaviour and inject a default value say an empty string or 0 depending on the column type when altering a table.

I'll leave this open for now.

[K4T]

Hi, do we have any progress with that bug?

[aldas]

having ['null' => false, 'default' => '']in DDLs created by create table is pretty insane. basically you allow creating rows with almost as bad as with null values enabled (there are cases when domain/business requirements have mandatory columns that can not have default values).

Maybe getColumnSqlDefinition could know in which ddl context it is called (CREATE/ALTER/...) and in sqlite not force you to use default?

[aldas]

I would like to point out that Sqlite ALTER TABLE does not support altering existing column. So there is no way you could alter NOT NULL column to NULL value - therefore I think CREATE should not force column NOT NULL with DEFAULT set.

and adding new column with NOT NULL and without DEFAULT would trigger database level error anyway

create table person (
   id integer PRIMARY KEY  NOT NULL,
   name text NULL
);

alter table person add column age integer not null;

SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL

there is no need to hold Phinx user hand (not null should not be tied to default in code) as sqlite will do it itself if query is faulty.

[fkooman]

Running into the same issue. I'd lilke to use Phinx to "migrate" from no database to a full up to date database with SQLite. The resulting SQLite "CREATE TABLE" syntax has issues...

$table = $this->table('foo');
$table->addColumn('bar', 'string', ['limit' => 255])->create();

CREATE TABLE `foo` (`id` INTEGER NULL PRIMARY KEY AUTOINCREMENT, `bar` VARCHAR(255) NULL);

The primary key has NULL which makes no sense, and the bar column has NULL even though that was not specified.

Expected behavior: no NULL for the primary key, and a NOT NULL instead of NULL for the bar column.

Update: as for the alter table, it makes perfect sense that you cannot add a column to an existing table, that possibly has data, without having a default value for the new column...

[aldas]

in my PR this is fixed.

        $table = new \Phinx\Db\Table('testtable', array(), $this->adapter);
        $table->addColumn('realname', 'string')
              ->save();

would result executed

CREATE TABLE testtable (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, realname VARCHAR(255) NOT NULL);

I point out that by design Phinx assumes that by default every column is defined as NOT NULL

https://github.com/robmorgan/phinx/blob/0.6.x-dev/src/Phinx/Db/Table/Column.php#L55

protected $null = false;

even tho Sqlite/Mysql defaults to NULL if no NULL/NOT NULL is specified in DDL.

[fkooman]

even tho Sqlite/Mysql defaults to NULL if no NULL/NOT NULL is specified in DDL.

I don't see this in my local MariaDB server though, there everything is NOT NULL as it should be, it is just in SQLite that the columns become/are NULL by default.

[dereuromark]

PR open and waiting to be merged.