0

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.

theDrifter
  • 1,631
  • 6
  • 24
  • 40
  • 1
    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. Note that `generated by default as identity` (and `generated always as identity` if supported), is the SQL standard way of defining a identity column. – Mark Rotteveel Mar 06 '18 at 12:01
  • Ah ok i did not know this. But after i open the table design GUI in LOB i see that the column id is set as a primary key when it has been created with "id generated by default as identity" – theDrifter Mar 06 '18 at 12:23

1 Answers1

0

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • ok that makes sense. LOB has some mechansim to check indeed the uniqueness of a inserted row. When i execute this statement INSERT INTO "people2"("ID", "NAME")VALUES ('0', 'Foo'); with the ID 0 already used for another record i get the error : Violation of unique constraint SYS_PK_81: duplicate value(s) for column(s) ID – theDrifter Mar 06 '18 at 12:37
  • 1
    @theDrifter Maybe LibreOffice implicitly adds a unique or primary key constraint, but looking purely at SQL specified behavior, it shouldn't. – Mark Rotteveel Mar 06 '18 at 12:52