Rails 3 ActiveRecord Insert Cannot Insert Value Null

Question

I am using:
Rails 3.0.9
MSSQL 2005

I have the table:

CREATE TABLE [dbo].[edocs](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [id_claim] [int] NOT NULL,
    [id_material] [smallint] NOT NULL,
    [is_secondary] [bit] NOT NULL CONSTRAINT [DF_edocs_is_secondary]  DEFAULT ((0)),
    [title] [varchar](100) COLLATE Ukrainian_CI_AS NOT NULL,
    [ext] [varchar](4) COLLATE Ukrainian_CI_AS NOT NULL,
    [size] [int] NOT NULL,
    [code] [varchar](10) COLLATE Ukrainian_CI_AS NULL,
    [receive_date] [datetime] NOT NULL CONSTRAINT [DF_edocs_receive_date]  DEFAULT (getdate()),
    [reg_date] [datetime] NULL,
    [reg_numb] [varchar](10) COLLATE Ukrainian_CI_AS NULL,
    [idcead] [int] NULL,
    [efile] [int] NULL

)

Some of fields has default value (for exapmle receive_date).
In Rails controller I try to create new record:

    Edoc.create(
        :id_claim => @claim_index, 
        :id_material => @doc_code, 
        :title => @file_list.first[:name],
        :ext => @file_list.first[:ext],
        :size => @file_list.first[:size],
        :code => @materials[@doc_code]["code"]
    )

But I get error message:

ActiveRecord::StatementInvalid (TinyTds::Error: Cannot insert the value NULL int
o column 'receive_date', table 'eFilling.dbo.edocs'; column does not allow nulls
. INSERT fails.: INSERT INTO [edocs] ([id_claim], [id_material], [is_secondary],
 [title], [ext], [size], [code], [receive_date], [reg_date], [reg_numb], [idCEAD
], [eFile]) VALUES (100000, 3, 0, N'text', N'rtf', 80
472, N'al', NULL, NULL, NULL, NULL, NULL)):

But In MSSQL 2005 console I can do that:

insert into edocs ([id_claim], [id_material],[title], [ext], [size]) values(1, 1, 'rrr', 'rtf', 123)

I don't want ActiveRecord auto completes the query by adding fields are not pointed in my create method.

How Can I do that?

score 0 · Accepted Answer · answered May 09 '12 at 18:10

0

I believe that even if you wrote straight SQL queries to insert, and didn't specify values for the "not null" fields, and they didn't have a default value, you would still throw a SQL error. I would suggest either allowing NULL on those fields or giving them a default value (maybe '' or 0?).

This isn't just a Rails thing, this comes down to the SQL queries themselves.

Example, for table with 'id', 'email' and 'name', where 'name' is not null and has no default value, this would fail:

INSERT INTO table (id, email) VALUES ('3', 'test@example.com')

As SQL passes NULL or emptiness as the value to 'name'

Think of it kind of like a 'required field' when it is not null without default value

answered May 09 '12 at 18:10

Kyle Macey

8,074
2
38
78

1

Thanks for advice. But MSSQL 2005 allows me to do **insert into edocs ([id_claim], [id_material],[title], [ext], [size]) values(1, 1, 'rrr', 'rtf', 123)** As you can see MSSQL allows me not specify field with default value in query. MSSQL automatically inserts default value (getdate()) – Lesha Pipiev May 09 '12 at 18:31
Then why not set the current Timestamp as the default value? – Kyle Macey May 09 '12 at 18:31
How can I do that? **Edoc.create(:receive_date => Time.now)**? – Lesha Pipiev May 09 '12 at 18:44
Set the default in your model. Look at this link: http://stackoverflow.com/a/1580847/628859 – Kyle Macey May 09 '12 at 18:52

Rails 3 ActiveRecord Insert Cannot Insert Value Null

1 Answers1