create_table(table_name, id: :primary_key, primary_key: nil, force: nil, **options) public

Creates a new table with the name table_name. table_name may either be a String or a Symbol.

There are two ways to work with #create_table. You can use the block form or the regular form, like this:

Block form

# create_table() passes a TableDefinition object to the block.
# This form will not only create the table, but also columns for the
# table.

create_table(:suppliers) do |t|
  t.column :name, :string, limit: 60
  # Other fields here
end

Block form, with shorthand

# You can also use the column types as method calls, rather than calling the column method.
create_table(:suppliers) do |t|
  t.string :name, limit: 60
  # Other fields here
end

Regular form

# Creates a table called 'suppliers' with no columns.
create_table(:suppliers)
# Add a column to 'suppliers'.
add_column(:suppliers, :name, :string, {limit: 60})

The options hash can include the following keys:

:id

Whether to automatically add a primary key column. Defaults to true. Join tables for {ActiveRecord::Base.has_and_belongs_to_many}[rdoc-ref:Associations::ClassMethods#has_and_belongs_to_many] should set it to false.

A Symbol can be used to specify the type of the generated primary key column.

:primary_key

The name of the primary key, if one is to be added automatically. Defaults to id. If :id is false, then this option is ignored.

If an array is passed, a composite primary key will be created.

Note that Active Record models will automatically detect their primary key. This can be avoided by using {self.primary_key=}[rdoc-ref:AttributeMethods::PrimaryKey::ClassMethods#primary_key=] on the model to define the key explicitly.

:options

Any extra options you want appended to the table definition.

:temporary

Make a temporary table.

:force

Set to true to drop the table before creating it. Set to :cascade to drop dependent objects as well. Defaults to false.

:if_not_exists

Set to true to avoid raising an error when the table already exists. Defaults to false.

:as

SQL to use to generate the table. When this option is used, the block is ignored, as are the :id and :primary_key options.

Add a backend specific option to the generated SQL (MySQL)
create_table(:suppliers, options: 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4')

generates:

CREATE TABLE suppliers (
  id bigint auto_increment PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Rename the primary key column
create_table(:objects, primary_key: 'guid') do |t|
  t.column :name, :string, limit: 80
end

generates:

CREATE TABLE objects (
  guid bigint auto_increment PRIMARY KEY,
  name varchar(80)
)
Change the primary key column type
create_table(:tags, id: :string) do |t|
  t.column :label, :string
end

generates:

CREATE TABLE tags (
  id varchar PRIMARY KEY,
  label varchar
)
Create a composite primary key
create_table(:orders, primary_key: [:product_id, :client_id]) do |t|
  t.belongs_to :product
  t.belongs_to :client
end

generates:

CREATE TABLE order (
    product_id bigint NOT NULL,
    client_id bigint NOT NULL
);

ALTER TABLE ONLY "orders"
  ADD CONSTRAINT orders_pkey PRIMARY KEY (product_id, client_id);
Do not add a primary key column
create_table(:categories_suppliers, id: false) do |t|
  t.column :category_id, :bigint
  t.column :supplier_id, :bigint
end

generates:

CREATE TABLE categories_suppliers (
  category_id bigint,
  supplier_id bigint
)
Create a temporary table based on a query
create_table(:long_query, temporary: true,
  as: "SELECT * FROM orders INNER JOIN line_items ON order_id=orders.id")

generates:

CREATE TEMPORARY TABLE long_query AS
  SELECT * FROM orders INNER JOIN line_items ON order_id=orders.id

See also TableDefinition#column for details on how to create columns.

Show source
Register or log in to add new notes.
September 25, 2008
22 thanks

All methods

create_table :table do |t|

  t.column # adds an ordinary column. Ex: t.column(:name, :string)
  t.index # adds a new index.
  t.timestamps
  t.change # changes the column definition. Ex: t.change(:name, :string, :limit => 80)
  t.change_default # changes the column default value.
  t.rename # changes the name of the column.
  t.references
  t.belongs_to
  t.string
  t.text
  t.integer
  t.float
  t.decimal
  t.datetime
  t.timestamp
  t.time
  t.date
  t.binary
  t.boolean
  t.remove
  t.remove_references
  t.remove_belongs_to
  t.remove_index
  t.remove_timestamps
end
September 9, 2010
2 thanks

bad idea.

Just a note, ypetya’s idea of using a before filter to set the primary key wont scale. transactions will eventually step on each other and probably end up with duplicate key ids, unless you have some other method to ensure uniqueness.

You’d be better off using mysql to generate the default integer primary key and have a secondary string “key” field.

February 5, 2010
2 thanks

An alternate way to have a string ID as a primary key

You can disable automatically created primary key and add it to manually with mysql:

The migration file:

def self.up

  create_table( :my_special_table, :id => false ) do |t|
    t.string :id, :limit => 5, :null => :no
  end

  execute "ALTER TABLE my_special_table ADD PRIMARY KEY (id)"

end

Then in a before_save filter you can generate the primary key for yourself.

Use a transaction and be aware of uniqueness!

June 28, 2011 - (v3.0.9)
0 thanks

available column types

Rails 3.0.9 update for RobinWu’s post

Available column types:

:string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean

Example:

MyClass < ActiveRecord::Migration

def change
  create_table :myclass do |t|
    t.string :name
    t.text :content
end

end

October 28, 2014
0 thanks

facing issue in create table (errno: 150)

I am trying to create table by writing the following code:

create_table :sam_server_user_audit do | t | t.column :user_id, :string, :limit => 100, :null => false t.column :user_type, :string, :limit => 20, :null => false t.column :status, :string, :limit => 20, :null => false t.column :created_at, :datetime, :null => false end

which generates:

CREATE TABLE `sam_server_user_audit` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `user_id` varchar(100) NOT NULL, `user_type` varchar(20) NOT NULL, `status` varchar(20) NOT NULL, `created_at` datetime NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id)) ENGINE=InnoDB CHARACTER SET `utf8`

here It is adding a foreign key user_id automatically.

How can I avoid that?