Create table syntax not working in hsql

Question

I am new to hsqldb. I am developing simple application to get the some input from user. So Searched for embedded database and found hsqldb is the solution for my requirement.

I have some create table syntax but it throws exception.

(This Query executed by using Netbeans Database services)

Query :

CREATE TABLE  company (
  comp_name varchar(100) NOT NULL,
  comp_id int(40) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (comp_id)
);

or

CREATE TABLE  company (
  comp_name varchar(100) NOT NULL,
  comp_id int(40) NOT NULL IDENTITY
);

hsql db throws the error :

Error code -5581, SQL state 42581: unexpected token: ( : line: 3
Line 2, column 1

Execution finished after 0 s, 1 error(s) occurred.

Kindly help me out..

Thanks in Advance..

Cheers...!

I have used the below query for my requirement. **CREATE TABLE company ( comp_name varchar(100) NOT NULL, comp_id INTEGER GENERATED BY DEFAULT AS IDENTITY(start with 1) );** — Dhinakar, Nov 03 '12 at 08:12

Yogendra Singh · Accepted Answer · 2012-11-03T06:53:58.467

20

Use INT or INTEGER without specify the field length as it is not required for Int type fields. It is required for VARCHAR and DECIMALetc. type fields.

 CREATE TABLE  company (
   comp_name varchar(100) NOT NULL,
   comp_id int
 );

To auto increment:

 ALTER TABLE company ALTER COLUMN comp_id 
 SET GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1);

Alternatively:

 CREATE TABLE  company (
   comp_name varchar(100) NOT NULL,
   comp_id int GENERATED BY DEFAULT AS IDENTITY 
                                         (START WITH 1, INCREMENT BY 1) NOT NULL
 );

You may also add the PRIMARY_KEY as below:

 CREATE TABLE  company (
   comp_name varchar(100) NOT NULL,
   comp_id INTEGER NOT NULL,
   PRIMARY KEY (comp_id)
 );

edited Nov 03 '12 at 06:53

answered Nov 03 '12 at 06:06

Yogendra Singh

33,927
6
63
73

Thanks.. If i execute the first query works fine. But the second query throw the exception **Error code -5532, SQL state 42532: primary key already exist Line 1, column 2** – Dhinakar Nov 03 '12 at 06:15
@DhinaKaran: `IDENTITY` keyword makes it for you. **I think you are fine with the first query itself**, otherwise try the updated second query. – Yogendra Singh Nov 03 '12 at 06:21
Ya. Its working But still I have a problem with insert query **INSERT INTO company (comp_name) VALUES('ABC');** says **Error code -10, SQL state 23502: integrity constraint violation: NOT NULL check constraint; SYS_CT_17649 table: COMPANY column: COMP_ID** – Dhinakar Nov 03 '12 at 06:26
For Query 1 : **Error code -5581, SQL state 42581: unexpected token: ( : line: 3** – Dhinakar Nov 03 '12 at 06:33
@DhinaKaran Try putting `NOT NULL` in the end and use GENERATED BY DEFAULT. – Yogendra Singh Nov 03 '12 at 06:41
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/19004/discussion-between-dhina-karan-and-yogendra-singh) – Dhinakar Nov 03 '12 at 06:44

Create table syntax not working in hsql

1 Answers1

Linked