CODEWITHSUNDEEP
sqlmonetdb
dasup
dasup

Reputation: 3835

How to implement AUTO_INCREMENT properly in MonetDB?

I have two MonetDB tables using auto-increment to generate unique IDs for each row. Each row in the child table refers to one row in the parent table (modeling a 1:n relationship):

CREATE SEQUENCE "test"."seq_parent" as integer START WITH 1;

CREATE TABLE "test"."parent" (
    "id" INT NOT NULL DEFAULT next value for "test"."seq_parent",
    "name" STRING,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE "test"."seq_child" as integer START WITH 1;

CREATE TABLE "test"."child" (
    "id" INT NOT NULL DEFAULT next value for "test"."seq_child",
    "parent_id" INT NOT NULL,
    "name" STRING,
    PRIMARY KEY ("id")
);

When I import (parent, child_1, …, child_n) tuples to this database, I need first to INSERT the parent and then INSERT all the children using the ID of the parent I just created.

The problem now is: How do I get the ID of the row I just INSERTed? In MySQL, there is LAST_INSERT_ID() for that. However, I could not find something like this in MonetDB.

I tried SELECT next value for "test"."seq_parent", which gives an ID but increments the sequence at the same time. I also tried SELECT MAX("id") FROM "test"."parent" right after executing the INSERT, but this is fragile as it breaks if another client is connected and inserts data to the parent table at the same time.

Upvotes: 0

Views: 494

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

If you can use application code, do so. First run a query to get the next value from seq_parent. Then set it to a variable. Then use that variable in all relevant insert commands.

Upvotes: 1

Related Questions