Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 959
Dhinakar Avatar asked Nov 03 '12 05:11

Dhinakar


People also ask

What is the use of HSQLDB?

HSQLDB is used for development, testing, and deployment of database applications. The main and unique feature of HSQLDB is Standard Compliance. It can provide database access within the user's application process, within an application server, or as a separate server process.

What is HSQLDB embedded?

HSQLDB (HyperSQL DataBase) is the leading SQL relational database system written in Java. It offers a small, fast multithreaded and transactional database engine with in-memory and disk-based tables and supports embedded and server modes. It includes a powerful command line SQL tool and simple GUI query tools.


1 Answers

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)
 );
like image 176
Yogendra Singh Avatar answered Nov 15 '22 16:11

Yogendra Singh