Search code examples
libreoffice-base

Libreoffice base. Identity keyword

To create a table with a primary key and a name with the SQL command line I do the following

CREATE TABLE "people"(
"id" integer primary key,
"name" varchar(255)
);

this creates a table where I have to worry about the correct setting of the primary key for each record.

CREATE TABLE "people2"(
"id" integer generated by default as identity,
"name" varchar(255)
);

The table people2 now take care for me for the primary key. It gets nicely autogenerated.

To change the table people to auto-fill the primary key I could do the following:

ALTER TABLE "people"
ALTER COLUMN "id" integer IDENTITY

The GUI becomes a bit buggy now (it shows the correct primary keys only after reopening the table). If I create a table like people2 I get the column id as the primary key.

Is there a nicer way to use primary key and auto increment in a create statement together? The constraint generated by default as identity was quite misleading at first.

Solution

  • The clause generated by default as identity (and generated always as identity if supported), is the SQL standard way of defining a identity column.

    Please be aware that in table "people2", you don't have a primary key. You only specified it as an identity column, you didn't make it a primary key. You need to specify this explicitly.