How to avoid inserting null values into Primary key or Not Null columns?

Question

I am inserting into a table from my jdbc program, like this

PreparedStatement ps = con.prepareStatement(sqlqry);
        ps.setInt(1,dto.getInstall_id());
        ps.setString(2, dto.getDashboard_name());
        ps.setString(3, dto.getDashboard_type());
        ps.setString(4, dto.getDashboard_image());

But in the table i have column say D_ID which in is primary key and i dont want o insert the D_ID from my program into table because the same id might be already exist. So for avoiding the PK_CONSTRAINT I am not inseting it. But when i try this i am getting this error.

 ORA-01400: cannot insert NULL into ("TESTDB"."TESTATBLE"."D_ID")

So how can i solve this problem, Any alternative like if i insert D_ID from the program my JDBC program the D_ID column should dynamically generate id's in the table. I am banging my head for this. Please help!

Have you made "Auto Increment" true for your database? If yes, then you need not insert id into table, DB will take care automatically. — Pradeep Simha, Nov 09 '13 at 11:18

score 5 · Accepted Answer · answered Nov 09 '13 at 12:31

You should create that ID using a sequence. So for each ID column that you have, you create a corresponding sequence:

create table testatble 
(
  d_id integer not null primary key,
  install_id integer not null,
  dashboard_name varchar(100)
  ... more columns ....
);

create sequence seq_testatble_d_id;

You can use it like this:

// note that there is no placeholder for the D_ID column
// the value is taken directly from the sequence
String sqlqry = 
"insert into testatble (d_id, install_id, dashboard_name)  " + 
"values (seq_testatble_d_id.nextval, ?, ?)";

PreparedStatement ps = con.prepareStatement(sqlqry);
ps.setInt(1,dto.getInstall_id());
ps.setString(2, dto.getDashboard_name());
... more parameters ...
ps.executeUpdate();

That way the id will be generated automatically.

If you need the generated ID in your Java code after the insert, you can use getGeneratedKeys() to return it:

// the second parameter tells the driver 
// that you want the generated value for the column D_ID
PreparedStatement ps = con.prepareStatement(sqlqry, new String[]{"D_ID"});

// as before
ps.setInt(1,dto.getInstall_id());
ps.setString(2, dto.getDashboard_name());
... more parameters ...
ps.executeUpdate();

// now retrieve the generated ID
int d_id = -1;
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) // important!
{
   d_id = rs.getInt(1);
}
rs.close();

More on sequences in the Oracle manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns002.htm#SQLRF00253

Thanx A lot for the usefull information with such a easy explanation. — mohammad aarif, Nov 10 '13 at 04:55

score 1 · Answer 2 · edited May 23 '17 at 12:33

1

You should use Auto Increment number for ID(I Oracle you can use sequence). You can do this at the link:

Create ID with auto increment on oracle

You should also read this. If there is a sequence to your ID then here you can read information about that.

edited May 23 '17 at 12:33

Community

1
1

answered Nov 09 '13 at 11:26

Gábor Csikós

2,787
7
31
57

How to avoid inserting null values into Primary key or Not Null columns?

2 Answers2