- Vertabelo
-
Ideas
HSQLDB Database schema generation is not correct...
CREATE TABLE Comments (
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
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.
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.
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.
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.
Customer support service by UserEcho
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.