CODEWITHSUNDEEP
sqloracle-database
dimas
dimas

Reputation: 2597

Insert error ORA - 00984

I inserted a simple sql script like the one below and its give me an error 

SQL Error: ORA-00984: column not allowed here
00984. 00000 -  "column not allowed here"

insert into tableA values (id, colA, colB) 
values ( (select max(id)+1 from tableA), 'First Name', 'Second Name');

Table definition

id - number(15,0)
colA - varchar2(50)
colB - varchar2(50)

The following script works on another table with the same table definition but just varies on the number of columns. I am guessing the (select max(id)+1 from tableA) is causing the error, can anyone help? or have other solutions for this? Thanks

Upvotes: 0

Views: 145

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

First, you only want one values clause

insert into tableA (id, colA, colB) 
values ( (select max(id)+1 from tableA), 'First Name', 'Second Name');

That will solve your immediate problem. That said, creating primary keys by doing the max(id) + 1 from the table is a horrible practice-- it doesn't work in a multi-user environment and it doesn't scale. You'd really, really want to create a sequence and use that sequence to generate your keys

create sequence tableA_seq
  start with 1
  increment by 1
  cache 20;

insert into tableA( id, colA, colB )
  values( tableA_seq.nextval, 'First Name', 'Second Name' );

Additionally, I'd strongly recommend that you not use id as the name of a column. Something like tableA_id makes much more sense. When you start writing queries that involve a number of different tables, it makes life far easier if tableA_id is the same name in the parent table and in all the child tables and when you don't have to work to figure out which id columns represent which concepts.

Upvotes: 4

Related Questions