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...!

Lamonicalamont

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) ); – Lamonicalamont

Answer 1

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)
 );

Ruffner

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 – Lamonicalamont

@DhinaKaran: IDENTITY keyword makes it for you. I think you are fine with the first query itself, otherwise try the updated second query. – Ruffner

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 – Lamonicalamont

For Query 1 : Error code -5581, SQL state 42581: unexpected token: ( : line: 3 – Lamonicalamont

@DhinaKaran Try putting NOT NULL in the end and use GENERATED BY DEFAULT. – Ruffner

let us continue this discussion in chat – Lamonicalamont