HSQLDB get next value for IDENTITY column
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.
I did some research and couldn't find anything sadly.
Example:
Suppose I have a table like this:
CREATE TABLE IF NOT EXISTS Names (
id INTEGER IDENTITY PRIMARY KEY,
name VARCHAR(16)
)
Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).
Edit:
I think I found a solution:
SELECT
IDENTITY_START
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'NAMES'
AND COLUMN_NAME = 'ID'
auto-increment identity hsqldb
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
|
show 1 more comment
I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.
I did some research and couldn't find anything sadly.
Example:
Suppose I have a table like this:
CREATE TABLE IF NOT EXISTS Names (
id INTEGER IDENTITY PRIMARY KEY,
name VARCHAR(16)
)
Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).
Edit:
I think I found a solution:
SELECT
IDENTITY_START
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'NAMES'
AND COLUMN_NAME = 'ID'
auto-increment identity hsqldb
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
From the manual: Example 4.1. inserting the next sequence value into a table row
– a_horse_with_no_name
Jun 13 '18 at 12:35
@a_horse_with_no_name That is talking about sequences. Not the table auto id values.
– BrainStone
Jun 13 '18 at 13:06
@a_horse_with_no_name I updated my question. I also might have found the soltion.
– BrainStone
Jun 13 '18 at 13:25
That gives the starting value of the underlying (unnamed) sequence. That is not the "next value". What do you need that "next value" for? If you need it to populate FK columns for subsequent inserts into other tables you can useidentity()to retrieve the generates value after inserting. Another option is to define theIDENTITYcolumn based on a (named) sequence.
– a_horse_with_no_name
Jun 13 '18 at 13:36
@a_horse_with_no_name the use case would be showing the user which ID will be next. And there are other related use cases.
– BrainStone
Jun 13 '18 at 13:48
|
show 1 more comment
I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.
I did some research and couldn't find anything sadly.
Example:
Suppose I have a table like this:
CREATE TABLE IF NOT EXISTS Names (
id INTEGER IDENTITY PRIMARY KEY,
name VARCHAR(16)
)
Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).
Edit:
I think I found a solution:
SELECT
IDENTITY_START
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'NAMES'
AND COLUMN_NAME = 'ID'
auto-increment identity hsqldb
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.
I did some research and couldn't find anything sadly.
Example:
Suppose I have a table like this:
CREATE TABLE IF NOT EXISTS Names (
id INTEGER IDENTITY PRIMARY KEY,
name VARCHAR(16)
)
Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).
Edit:
I think I found a solution:
SELECT
IDENTITY_START
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'NAMES'
AND COLUMN_NAME = 'ID'
auto-increment identity hsqldb
auto-increment identity hsqldb
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
edited Jun 13 '18 at 13:47
BrainStone
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
asked Jun 13 '18 at 11:15
BrainStoneBrainStone
1086
1086
From the manual: Example 4.1. inserting the next sequence value into a table row
– a_horse_with_no_name
Jun 13 '18 at 12:35
@a_horse_with_no_name That is talking about sequences. Not the table auto id values.
– BrainStone
Jun 13 '18 at 13:06
@a_horse_with_no_name I updated my question. I also might have found the soltion.
– BrainStone
Jun 13 '18 at 13:25
That gives the starting value of the underlying (unnamed) sequence. That is not the "next value". What do you need that "next value" for? If you need it to populate FK columns for subsequent inserts into other tables you can useidentity()to retrieve the generates value after inserting. Another option is to define theIDENTITYcolumn based on a (named) sequence.
– a_horse_with_no_name
Jun 13 '18 at 13:36
@a_horse_with_no_name the use case would be showing the user which ID will be next. And there are other related use cases.
– BrainStone
Jun 13 '18 at 13:48
|
show 1 more comment
From the manual: Example 4.1. inserting the next sequence value into a table row
– a_horse_with_no_name
Jun 13 '18 at 12:35
@a_horse_with_no_name That is talking about sequences. Not the table auto id values.
– BrainStone
Jun 13 '18 at 13:06
@a_horse_with_no_name I updated my question. I also might have found the soltion.
– BrainStone
Jun 13 '18 at 13:25
That gives the starting value of the underlying (unnamed) sequence. That is not the "next value". What do you need that "next value" for? If you need it to populate FK columns for subsequent inserts into other tables you can useidentity()to retrieve the generates value after inserting. Another option is to define theIDENTITYcolumn based on a (named) sequence.
– a_horse_with_no_name
Jun 13 '18 at 13:36
@a_horse_with_no_name the use case would be showing the user which ID will be next. And there are other related use cases.
– BrainStone
Jun 13 '18 at 13:48
From the manual: Example 4.1. inserting the next sequence value into a table row
– a_horse_with_no_name
Jun 13 '18 at 12:35
From the manual: Example 4.1. inserting the next sequence value into a table row
– a_horse_with_no_name
Jun 13 '18 at 12:35
@a_horse_with_no_name That is talking about sequences. Not the table auto id values.
– BrainStone
Jun 13 '18 at 13:06
@a_horse_with_no_name That is talking about sequences. Not the table auto id values.
– BrainStone
Jun 13 '18 at 13:06
@a_horse_with_no_name I updated my question. I also might have found the soltion.
– BrainStone
Jun 13 '18 at 13:25
@a_horse_with_no_name I updated my question. I also might have found the soltion.
– BrainStone
Jun 13 '18 at 13:25
That gives the starting value of the underlying (unnamed) sequence. That is not the "next value". What do you need that "next value" for? If you need it to populate FK columns for subsequent inserts into other tables you can use
identity() to retrieve the generates value after inserting. Another option is to define the IDENTITY column based on a (named) sequence.– a_horse_with_no_name
Jun 13 '18 at 13:36
That gives the starting value of the underlying (unnamed) sequence. That is not the "next value". What do you need that "next value" for? If you need it to populate FK columns for subsequent inserts into other tables you can use
identity() to retrieve the generates value after inserting. Another option is to define the IDENTITY column based on a (named) sequence.– a_horse_with_no_name
Jun 13 '18 at 13:36
@a_horse_with_no_name the use case would be showing the user which ID will be next. And there are other related use cases.
– BrainStone
Jun 13 '18 at 13:48
@a_horse_with_no_name the use case would be showing the user which ID will be next. And there are other related use cases.
– BrainStone
Jun 13 '18 at 13:48
|
show 1 more comment
1 Answer
1
active
oldest
votes
You can get the last identity value already inserted, but not the next one.
Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,
INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'
See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions
answered 4 hours ago
fredtfredt
1655
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f209518%2fhsqldb-get-next-value-for-identity-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can get the last identity value already inserted, but not the next one.
Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,
INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'
See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions
answered 4 hours ago
fredtfredt
1655
add a comment |
You can get the last identity value already inserted, but not the next one.
Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,
INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'
See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions
answered 4 hours ago
fredtfredt
1655
add a comment |
You can get the last identity value already inserted, but not the next one.
Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,
INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'
See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions
answered 4 hours ago
fredtfredt
1655
You can get the last identity value already inserted, but not the next one.
Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,
INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'
See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions
answered 4 hours ago
fredtfredt
1655
answered 4 hours ago
fredtfredt
1655
answered 4 hours ago
fredtfredt
1655
answered 4 hours ago
fredtfredt
1655
1655
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f209518%2fhsqldb-get-next-value-for-identity-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
From the manual: Example 4.1. inserting the next sequence value into a table row
– a_horse_with_no_name
Jun 13 '18 at 12:35
@a_horse_with_no_name That is talking about sequences. Not the table auto id values.
– BrainStone
Jun 13 '18 at 13:06
@a_horse_with_no_name I updated my question. I also might have found the soltion.
– BrainStone
Jun 13 '18 at 13:25
That gives the starting value of the underlying (unnamed) sequence. That is not the "next value". What do you need that "next value" for? If you need it to populate FK columns for subsequent inserts into other tables you can use
identity()to retrieve the generates value after inserting. Another option is to define theIDENTITYcolumn based on a (named) sequence.– a_horse_with_no_name
Jun 13 '18 at 13:36
@a_horse_with_no_name the use case would be showing the user which ID will be next. And there are other related use cases.
– BrainStone
Jun 13 '18 at 13:48