Let me demonstrate my problem with a simple test case.
Test 1:
CREATE SEQUENCE SEQ;
CREATE TABLE A(X INT DEFAULT NEXT VALUE FOR SEQ);
CREATE TABLE B(Y INT DEFAULT NEXT VALUE FOR SEQ);
INSERT INTO A VALUES(1);
INSERT INTO A VALUES(DEFAULT);
SELECT * FROM A;
X
---
1
1
Problem: X is not unique
Test 2:
DROP TABLE A;
DROP TABLE B;
DROP SEQUENCE SEQ;
CREATE SEQUENCE SEQ;
CREATE TABLE A(X INT DEFAULT NEXT VALUE FOR SEQ UNIQUE);
CREATE TABLE B(Y INT DEFAULT NEXT VALUE FOR SEQ UNIQUE);
INSERT INTO A VALUES(1);
INSERT INTO A VALUES(DEFAULT);
Unique index or primary key violation: "CONSTRAINT_INDEX_4 ON
TEST.A(X)"; SQL statement:
INSERT INTO A VALUES(DEFAULT) [23001-143] 23001/23001
Problem: If someone inserts an explicit ID then afterwards an insert
that expects to have
an automatically generated ID receives an error.
- Rami
Thomas Mueller wrote:
Hi,
Why not? I think a sequence can do all that.
No it can't.
Why can't you use a sequence? As in:
create sequence seq;
create table a(x int default next value for seq);
create table b(y int default next value for seq);
This assumes you didn't specify the value when inserting into the
table, or used the term "default" (as documented):
insert into a values(default);
update a set x = default;
There is also a non-standard way to automatically convert null to default:
create table a(x int default next value for seq null_to_default);
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
