0
Completed

HSQLDB Database schema generation is not correct...

Khurram Shakir 10 years ago updated by Adam Mościcki 7 years ago 9
I created a sample database diagram for HSQLDB 2.3 , The schema generated in SQL is following for a table,

CREATE TABLE Comments (
Id bigint GENERATED ALWAYS AS IDENTITY NOT NULL CONSTRAINT Comments_Id,
EntityId bigint NULL,
EntityType varchar(100) NOT NULL,
Subject varchar(200) NOT NULL,
CreationDate timestamp NOT NULL,
UserName varchar(200) NOT NULL,
CONSTRAINT Id PRIMARY KEY (Id) );

When I tried to run it on HSQL, it is giving me error that "ID" is duplicate, What I found is Constraint should be named something else otherwise HSQL gives error since it is same as column name, if I renamed that constraint ID instead of ID_PK , it is working fine.

2. Also based on HSQL documentation, Primary key can be defined as simply marking as
Id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

instead of explicitly define the constraint. please confirm that I am not missing anything and this is bug.

Answer

Answer
Hi, 

I've looked on your model and reproduced  error.  Tables "attachment" and "comments" have the same name of primary key constraint. Primary key constraint name must be unique. Please change the name or "Unset" property. Check other tables also. 

Under review
What is the version of HSQLDB you are using? I cannot reproduce the problem, neither with constraint name "id", nor "id_pk".

As for 2, you can define primary key as
Id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
but Vertabelo is prepared to handle multi-column primary keys. This is why it generates primary key as constraint.
I can't reproduce the error with HSQLDB 2.3.2 either. Could you post the exact error message that you get / the screeshots with the error message?
I tried to run it using HSQLDB 2.3.2, it is running on Network mode. my client is Netbeans 8.1 , when tried to run script I got Following error,

Executed successfully in 0.002 s, 0 rows affected.
Line 13, column 1


Error code -5504, SQL state 42504: object name already exists: ID in statement [CREATE TABLE Comments (
Id bigint GENERATED ALWAYS AS IDENTITY NOT NULL CONSTRAINT Comments_Id,
EntityId bigint NULL,
EntityType varchar(100) NOT NULL,
Subject varchar(200) NOT NULL,
Text varchar(2000) NOT NULL,
CreationDate timestamp NOT NULL,
UserName varchar(200) NOT NULL,
CONSTRAINT Id PRIMARY KEY (Id)
)]
Line 19, column 1


Error code -5504, SQL state 42504: object name already exists: ID in statement [CREATE TABLE Objective (
Id bigint NOT NULL,
Name varchar(100) NOT NULL,
CreationDate timestamp NOT NULL,
Status varchar(50) NOT NULL,
CONSTRAINT Id PRIMARY KEY (Id)
)]
Line 31, column 1


Error code -5504, SQL state 42504: object name already exists: ID in statement [CREATE TABLE ObjectiveTasks (
Id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
ObjectiveId bigint NOT NULL,
TaskGroupId bigint NOT NULL,
CONSTRAINT Id PRIMARY KEY (Id)
)]
Line 40, column 1


Error code -5504, SQL state 42504: object name already exists: ID in statement [CREATE TABLE Tag (
Id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
Name varchar(50) NOT NULL,
EntityType varchar(50) NOT NULL,
EntityId bigint NOT NULL,
CONSTRAINT Id PRIMARY KEY (Id)
)]
Line 48, column 1


Error code -5581, SQL state 42581: unexpected token: DEFAULT : line: 15
Line 57, column 1


Error code -5504, SQL state 42504: object name already exists: ID in statement [CREATE TABLE TaskGroup (
Id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
Name varchar(100) NOT NULL,
CONSTRAINT Id PRIMARY KEY (Id)
)]
Line 76, column 1


Error code -5504, SQL state 42504: object name already exists: ID in statement [CREATE TABLE TaskOrder (
Id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
PreviousTaskId bigint NULL,
NextPreviousId bigint NULL,
currentTaskId bigint NOT NULL,
CONSTRAINT Id PRIMARY KEY (Id)
)]
Line 83, column 1


Executed successfully in 0.002 s, 0 rows affected.
Line 92, column 1


Error code -5501, SQL state 42501: user lacks privilege or object not found: PUBLIC.OBJECTIVETASKS
Line 109, column 1


Error code -5501, SQL state 42501: user lacks privilege or object not found: PUBLIC.OBJECTIVETASKS
Line 117, column 1


Error code -5501, SQL state 42501: user lacks privilege or object not found: PUBLIC.TASKORDER
Line 125, column 1


Error code -5501, SQL state 42501: user lacks privilege or object not found: PUBLIC.TASK
Line 133, column 1


Error code -5501, SQL state 42501: user lacks privilege or object not found: PUBLIC.TASK
Line 141, column 1


Error code -5501, SQL state 42501: user lacks privilege or object not found: PUBLIC.TASK
Line 149, column 1


Execution finished after 0.004 s, 13 error(s) occurred.

Answer
Hi, 

I've looked on your model and reproduced  error.  Tables "attachment" and "comments" have the same name of primary key constraint. Primary key constraint name must be unique. Please change the name or "Unset" property. Check other tables also. 

I got it, I thought it is something to make a automatic generation of primary key name in new  tables. and then I forget to remove it.
Thank you for your response. I really appreciate it.

I'm trying to create a table and insert some data into it using Java 8, HSQLDB 2.4.0 working through Eclipse Neon.
I keep getting the following error:

"integrity constraint violation: NOT NULL check constraint; SYS_PK_10091 table: TRANSACTIONS column: ID"


Here's my code:

CREATE TABLE IF NOT EXISTS TRANSACTIONS ( tabKey IDENTITY PRIMARY KEY, "
+ "transaction_location varchar(20) NOT NULL, "
              + "account_number varchar(40) NOT NULL, "
              + "transaction_type varchar(20) NOT NULL, "
              + "account_balance varchar(20) NOT NULL, "
              + "transaction_amount varchar(20) NOT NULL, "
              + "out_of_network_charge varchar(5) NOT NULL, "
              + "new_account_balance varchar(20) NOT NULL, "
              + "date_time_stamp varchar(40) NOT NULL, "
              + "ip_address varchar(20) NOT NULL, "

              + "port_number varchar(20) NOT NULL )"


Here's my insert code:


String insert_string = "INSERT INTO TRANSACTIONS (transaction_location, account_number, transaction_type, account_balance, transaction_amount, out_of_network_charge, new_account_balance, date_time_stamp, ip_address, port_number) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        psInsert = conn.prepareStatement(insert_string);
       
        psInsert.setString(1, transaction.getTransLocation());
        psInsert.setString(2, transaction.getAccountNumber());
        psInsert.setString(3, transaction.getTransType());
        psInsert.setString(4, transaction.getAccountBalance());
        psInsert.setString(5, transaction.getTransAmount());
        psInsert.setString(6, transaction.getOutOfNetworkCharge());
        psInsert.setString(7, transaction.getNewBalance());
        psInsert.setString(8, transaction.getDateTime());
        psInsert.setString(9, transaction.getNodeIpAddress());
        psInsert.setString(10, transaction.getNodePortNumber());
        psInsert.executeUpdate();

        conn.commit();


Am I doing something wrong?

Hello,


Is your problem is in someway related to our tool: my.vertabelo.com?

I am sorry, but we do not provide consulting services.


Best regards,

Adam Mościcki


PS: You need to add tabKey to the INSERT statment.