CODEWITHSUNDEEP
pythonsqloracle-databaseoracle-sqldevelopercx-oracle
pymat
pymat

Reputation: 1192

IntegrityError: ORA-01400: cannot insert NULL into

I'm trying to parse the following parameters into cursor.execute:

sql_insert = 'insert into sms_messaging (result_sms, msgid, msgparts) values (:1, :2, :3)'
smsreport_text_new = ('success', '2D67C865FB6FA25A9261C75E8D2F0F2B ', 1)

cursor.execute(sql_insert, smsreport_text_new)

Afterwards I'm receiving the following error message:

IntegrityError: ORA-01400: cannot insert NULL into ("ADAUTH"."SMS_MESSAGING"."DATE_SMS")

In order to build the table, my .sql file looks like this:

-- Create template
CREATE TABLE sms_messaging(
  date_sms DATE primary key,
  result_sms VARCHAR(20),
  msgid VARCHAR(128),
  msgparts INTEGER,
  errorcode INTEGER,
  errormessage VARCHAR(128)
);

Even though for this particular sql_insert there is no date_sms, the error message indicates an issue with this. I don't see where I'm going wrong. How can I resolve this issue?

Upvotes: 0

Views: 2139

Answers (3)

J. Chomel
J. Chomel

Reputation: 8395

You should not have DATE_SMS as primary key! Rather use msg_id:

CREATE TABLE sms_messaging(
  date_sms DATE,
  result_sms VARCHAR(20),
  msgid VARCHAR(128) primary key,
  msgparts INTEGER,
  errorcode INTEGER,
  errormessage VARCHAR(128)
);

Upvotes: 1

rory.ap
rory.ap

Reputation: 35318

The problem is that the DATE_SMS column is the primary key, and therefore it doesn't accept a NULL value. Since you haven't provided a value in your insert statement for that column, that's why you're getting the error.

I'm not sure about oracle, but in SQL Server, for example, you can setup a default value for a column, so I would, for example, set 'GETDATE()` as the default for a date column. Maybe Oracle has that too.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270573

The problem is that you have a column DATE_SMS that is declared NOT NULL and has no default value. You need to give it a value. I don't know what it should be, but perhaps something like this:

insert into sms_messaging (result_sms, msgid, msgparts, date_sms)
    values (:1, :2, :3, sysdate);

This would put the current date/time into the field.

Upvotes: 4

Related Questions