Adding Columns

Adding columns to database tables

Introduction

The migration framework delegates all operations to your RDBMS-specific adapter, which creates the exact SQL needed for your RDMBS.

Because migrations are portable across RDBMSs, the definitions for tables and columns need to be expressed in a higher-level "language", and not RDBMS-specific SQL.

This page documents the syntax for adding and modifying columns in your database.

Details

All columns in a database are really just specific types of a generalized form. That is, a varchar(32) column is really just a string that happens to have a max-length of 32. Same for int(8), its just a number with a maximum value of 99999999.

The framework can take advantage of this fact to offer generalized types (with sane defaults) and the support for specifying exact details.

NOTE: The documentation below for adding columns is the same whether you are adding a column via a TableDefinition result object (using the column() method on the result object of a create_table()) or directly using the add_column() method.

The supported types are:

MySQL

Generalized Type	Maps to MySQL Type	Extras
string	varchar
text	text
mediumtext	mediumtext
tinyinteger	tinyint
smallinteger	smallint
mediuminteger	mediumint
biginteger	bigint
integer	int	11
float	float
decimal	decimal
datetime	datetime
time	time
date	date
binary	blob
tinybinary	tinyblob
mediumbinary	mediumblob
longbinary	longblob
enum	enum
uuid	char	limit=36
boolean	tinyint	limit=1
char	char

Postgres

Generalized Type	Maps to Postgres Type	Extras
string	varchar	limit=255
text	text
tinytext	text
mediumtext	text
integer	integer
tinyinteger	smallint
mediuminteger	integer
smallinteger	smallint
biginteger	bigint
float	float
decimal	decimal	scale=10, precision=0
datetime	timestamp
timestamp	timestamp
time	time
date	date
binary	bytea
tinybinary	bytea
mediumbinary	bytea
longbinary	bytea
boolean	boolean
tsvector	tsvector
uuid	uuid

SQLite

Generalized Type	Maps to SQLite Type	Extras
string	varchar	limit=255
text	text
tinytext	text
mediumtext	text
integer	integer
tinyinteger	smallint
smallinteger	smallint
mediuminteger	integer
biginteger	bigint
float	float
decimal	decimal	scale=0, precision=10
datetime	datetime
timestamp	datetime
time	time
date	date
binary	blob
tinybinary	blob
mediumbinary	blob
longbinary	blob
boolean	boolean

All column types have defaults with support for specifying details. For example, all columns declared as string will default to 255 characters.

Lets get started with some examples already!

Default assumptions All column definitions start out with some default assumptions, which you can override as needed.

• Columns are NULL by default

Lets see an example to get started (all examples below, assume that $users is the result of calling $this->create_table("users");):

Example A:

Create a column called first_name with a default length of 255:

   $users->column("first_name", "string");

This generates the SQL:

    `first_name` varchar(255) NULL

Example B: Create a column called first_name with a max-length of 32 ( implicitly allow NULL values):

    $users->column("first_name", "string", array('limit' => 32));

This generates the SQL:

    `first_name` varchar(32) NULL

Example C: Create a column called first_name with a max-length of 64, a default value of 'mr.' and do not allow NULL values:

    $users->column("first_name", "string", array('limit' => 64, 'default' => 'mr.', 'null' => false));

This generates the SQL:

    `first_name` varchar(64) DEFAULT 'mr.' NOT NULL

Example D: Create a numeric column called post_count with a max-length of 4 digits, a default value of 0 and NULL values not allowed:

    $users->column("post_count", "integer", array('limit' => 4, 'default' => 0, 'null' => false));

This generates the SQL:

    `post_count` integer(4) DEFAULT 0 NOT NULL

Example E: Create a datetime column called created_at and NULL values not allowed:

    $users->column("created_at", "datetime", array('null' => false));

This generates the SQL:

    `created_at` datetime NOT NULL

Example F: Specify your own key primary key. By default the framework will generate an 'id' column as a primary key, but if you would like to specify your own primary key column pass a value of Boolean true into the 'primary_key' option. This requires that you in turn specify 'id' => false into the create_table options.

    $t = $this->create_table('users', array('id' => false, 'options' => 'Engine=InnoDB'));
    $t->column('guid', 'string', array('primary_key' => true));
    $t->finish();

MySQL Extras

MySQL supports adding a column after an existing column so you can place your new column positionally.

Example: lets say you had an animals table with two columns name and hair_color and you wanted to add a NEW column after name (normally adding a new column would just append it to the end). Here is how you can do this with MySQL specific DDL:

$this->add_column('animals', 'favorite_food', 'string', array('after' => 'name'));

In addition MySQL supports unsigned integers:

$this->add_column('animals', 'age', 'integer', array('unsigned' => true));