Hello,
I am trying to make a table with an Id column and a Name column.
I want the Id column to increment by 1 everytime a Name is added that doesn't already exist in the table. If the name already exists, it should do nothing.
This is the create table statement that I have used:
CREATE TABLE "TEST_AUTOINCREMENT"
( "Id" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE ,
"Name" VARCHAR2(50 BYTE)
) ;
When I have inserted "Jack" and "Jane" the table looks like this:
I used the following script to check if a name already exists:
MERGE INTO TEST_autoincrement t
USING (SELECT 'Jane' AS "Name" FROM DUAL
) s
ON (t."Name" = s."Name")
WHEN NOT MATCHED THEN
INSERT("Name")
VALUES(s."Name")
;
If I run this I would expect that the table will still look the same, because "Jane" is already a value in the Name column. That works fine, the table looks still the same.
However, when I insert a new Name, for instance "Bill" (after I tried to insert "Jane" for the second time), I would expect that "Bill" would get Id = 3.
So I run the following code:
MERGE INTO TEST_autoincrement t
USING (SELECT 'Bill' AS "Name" FROM DUAL
) s
ON (t."Name" = s."Name")
WHEN NOT MATCHED THEN
INSERT("Name")
VALUES(s."Name")
;
Now the entire table looks like this, so Bill got Id = 4 instead of 3:
Now my question is:
Why did "Bill" get Id = 4? How can I make sure that "Bill" gets Id = 3?
How can I get a sequence without gaps?
Best regards,
Maarten