Using sql to create primary key in a field

[marus_b]

Hello everyone,

I am trying to change a field in a table in an embedded HSQLDB database, using SQL commands, to be the primary key and to change it to autovalue. The latter is giving me an error, which I cannot resolve.

DROP TABLE “emp” IF EXISTS;
CREATE TABLE “emp” (“id” INTEGER NOT NULL);
ALTER TABLE “emp” ALTER COLUMN “id” GENERATED BY DEFAULT AS IDENTITY
ALTER TABLE “emp” ADD PRIMARY KEY (“id”)

I have no problem creating the autovalue and primary key at the time of the table declaration, but when I try to do it later it gives me a wrong data type error.

[Ratslinger]

Hello,
You could:

DROP TABLE "emp" IF EXISTS;
CREATE TABLE "emp" ("id" INTEGER NOT NULL, "First" Varchar(50) NOT NULL);
ALTER TABLE "emp" ADD PRIMARY KEY ("id")
ALTER TABLE "emp" ALTER COLUMN "id" IDENTITY;

or

DROP TABLE "emp" IF EXISTS;
CREATE TABLE "emp" ("id" INTEGER IDENTITY PRIMARY KEY, "First" Varchar(50) NOT NULL);

[marus_b]

Thank you Ratslinger.

It looks like in your first solution (which was precisely what I was after), the order of declaration makes a difference. In other words, the primary key has to be declared first, and subsequently the autovalue can be initiated. I did not realize that.

[Ratslinger]

@marus_b
An auto increment field in this table must be at least a part of the primary key. Also, the table can have no more than one auto increment field. This is why you must specify it as the primary key first.

[marus_b]

To elaborate on this topic, what would the syntax be if primary key consists of two fields?

DROP TABLE “tbl-EmployeePhoneNumbers” IF EXISTS;

CREATE TABLE “tbl-EmployeePhoneNumbers” (
“emp_ID” INT NOT NULL,
“Number” VARCHAR(14) NOT NULL,
“Description” VARCHAR(100)
);

ALTER TABLE “tbl-EmployeePhoneNumbers” ADD PRIMARY KEY (“emp_ID”, “Number”)

ALTER TABLE “tbl-EmployeePhoneNumbers” ALTER COLUMN “emp_ID” IDENTITY;

The results renders the following message:

[Ratslinger]

Sorry but I may be mistaken about the auto increment field as part of a multi-field primary key in HSQLDB embedded. May have gotten this from something else. Test seemed to work with GUI creation but upon reopening the auto increment is gone.


Edit:

MySQL, for one, has some capability:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

.
Info obtained from → mysql two column primary key with auto-increment - Stack Overflow

[marus_b]

Are you implying that a split HSQLDB would allow for multi-field primary key without any issues?

[Ratslinger]

@marus_b

Are you implying that a split HSQLDB would allow

No. Simply stating my error with HSQLDB embedded but my thinking it was a valid combination does work at least with MySQL v8.0.31 on Ubuntu 20.04
.
It may (and most likely does) work in other databases. May try some other (PostgreSQL and/or HSQLDB newer version) later or tomorrow.

[Ratslinger]

Quick test with HSQLDB 2.3.2 using:

CREATE TABLE sample_table 
( 
  hash  VARCHAR(20), 
  id    INTEGER GENERATED BY DEFAULT AS IDENTITY, 
  data  VARCHAR(256), 
  PRIMARY KEY(hash, id) 
);

from java - Compound primary key with autoincrement part - Stack Overflow
.
giving:

Edit: BTW your SQL worked with the 2.3.2 split:

[marus_b]

Thank you once again.