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

**URL:** <https://www.ruby-forum.com/t/dont-use-type-as-a-db-column-name/148721>\
**Category:** Rails\
**Created:** [September 16, 2008, 4:27pm UTC](https://www.ruby-forum.com/t/dont-use-type-as-a-db-column-name/148721 "2008-09-16T16:27:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Commander\_J](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a8b319/32.png) [@Commander\_J](https://www.ruby-forum.com/u/Commander_J)\
**Post date:** [September 16, 2008, 4:27pm UTC](https://www.ruby-forum.com/t/dont-use-type-as-a-db-column-name/148721/1 "2008-09-16T16:27:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Commander\_J](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a8b319/32.png) [@Commander\_J](https://www.ruby-forum.com/u/Commander_J)\
**Post date:** [September 16, 2008, 4:34pm UTC](https://www.ruby-forum.com/t/dont-use-type-as-a-db-column-name/148721/2 "2008-09-16T16:34:05Z")

</div>

“type” is a reserved field name for single table inheritance (STI):  
[http://wiki.rubyonrails.com/rails/pages/MagicFieldNames](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

---

<div class="post-metadata">

**Author:** ![Commander\_J](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/a8b319/32.png) [@Commander\_J](https://www.ruby-forum.com/u/Commander_J)\
**Post date:** [September 16, 2008, 5:31pm UTC](https://www.ruby-forum.com/t/dont-use-type-as-a-db-column-name/148721/3 "2008-09-16T17:31:41Z")

</div>

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.