Don't use 'type' as a DB column name

Rails
1

Hi,
I’m creating a simple Migration that adds test data to my database.

A Form has many Elements and every Element belongs to a Form. An element
has
the mandatory columns name, type and value.

class AddTestData < ActiveRecord::Migration
def self.up
Form.delete_all
Element.delete_all

bert = Form.create(:name => "Bert's Form")
Element.create(:form => bert, :name => 'First Name', :type =>

‘text’,
:value => ‘’)
Element.create(:form => bert, :name => ‘Last Name’, :type => ‘text’,
:value => ‘’)

ernie = Form.create(:name => "Ernie's Form")
Element.create(:form => ernie, :name => 'Your N.', :type =>

‘text’,
:value => ‘’)
Element.create(:form => ernie, :name => ‘E-mail’, :type => ‘text’,
:value => ‘’)
end

def self.down
Form.delete_all
Element.delete_all
end
end

Gives the error:

Mysql::Error: #23000Column ‘type’ cannot be null: INSERT INTO elements
(form_id, ordering, name, title, type, value, flags,
created_at, updated_at) VALUES(2, NULL, ‘First Name’, NULL, NULL,
‘’,
NULL, ‘2008-09-16 13:59:36’, ‘2008-09-16 13:59:36’)

As you can see, the ‘type’ column somehow isn’t inserted.

I had similar issues when trying to add a column called ‘timestamp’.

Best regards,
CmdJohnson

2

“type” is a reserved field name for single table inheritance (STI):
http://wiki.rubyonrails.com/rails/pages/MagicFieldNames

We’re using “timestamp” without a problem in some of our apps, so that
must be another issue.

On 16 Sep 2008, at 16:09, Commander J. wrote:

Element.delete_all
Element.create(:form => ernie, :name => 'E-mail', :type =>

Gives the error:

Mysql::Error: #23000Column ‘type’ cannot be null: INSERT INTO
elements (form_id, ordering, name, title, type, value,
flags, created_at, updated_at) VALUES(2, NULL, ‘First Name’,
NULL, NULL, ‘’, NULL, ‘2008-09-16 13:59:36’, ‘2008-09-16 13:59:36’)

As you can see, the ‘type’ column somehow isn’t inserted.

I had similar issues when trying to add a column called ‘timestamp’.

Best regards

Peter De Berdt

3

Commander J. wrote:
…snip…

Gives the error:

Mysql::Error: #23000Column ‘type’ cannot be null: INSERT INTO elements
(form_id, ordering, name, title, type, value, flags,
created_at, updated_at) VALUES(2, NULL, ‘First Name’, NULL, NULL,
‘’,
NULL, ‘2008-09-16 13:59:36’, ‘2008-09-16 13:59:36’)

As you can see, the ‘type’ column somehow isn’t inserted.

From looking at that error it’s clear that the 5th value sent is NULL
which is your type column. So your problem is not with MySQL. Given
that Rails uses a “type” column for single-table inheritance, maybe that
has something to do with your issue.

I had similar issues when trying to add a column called ‘timestamp’.
MySQL has a TIMESTAMP data type. I would guess that you could use that
as a column name as long as it’s quoted properly (i.e. timestamp).

I tend to avoid naming columns with likely database keywords (type,
timestamp, data, time, number, text, etc.).

P.S. I also avoid using TIMESTAMP data type in MySQL. It’s may not
behave as you might expect it to. If a column of that type is not send
in a UPDATE statement it will automatically update itself to the current
time of the database server.