Search code examples
javahsqldbcreate-table

Create table syntax not working in hsql


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


Solution

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