What happens to the Identity value when you Truncate a table in Exasol?

Today was challenging at work for many reasons, but it was a good day for learning new things!

I thought I’d share with you a nugget that I learnt today.

When I truncate a table, I expect the all of the data to be permanently deleted, essentially like it was never there. For all indexes to be emptied, and any auto-incrementing constraints to be reset.

Let’s give it a go then… *simples*…no?

CREATE TABLE ID_TESTING(
ID DECIMAL(18,0) IDENTITY NOT NULL ENABLE,
A DECIMAL(18,0),
B DECIMAL(18,0)
);

I then insert 3 rows:

insert into ID_TESTING (A,B)
SELECT 1,2;
insert into ID_TESTING (A,B)
SELECT 2,2;
insert into ID_TESTING (A,B)
SELECT 3,2;

This gives me 3 rows, with an identity value of 3, which we can check by looking in one of the sys tables.

SELECT column_identity
FROM SYS.EXA_ALL_COLUMNS
WHERE column_schema = 'DUMMY'
AND column_table = 'ID_TESTING'
AND column_name = 'ID';

Finally, I truncate the table;

truncate table ID_TESTING;

So if we check the identity value this should be zero right? Using the same query as above, we can see it remains at 3.

This is obviously not ideal, if you’re loading large amounts of data, or have not set your identity column to be DECIMAL(18,0), or you’re just plain expecting a counter to start at 1. Having got in touch with Exasol about this, as it just doesn’t make sense to me, it seems it had been raised as an issue previously, logged as IDEA 177, but has been discounted. As such, it makes this behaviour, accepted.

If, like me, this doesn’t really work for you; we need a workaround…

…I’ll cover it in my next blog post!

 

Error: [27002] constraint violation – primary key (PK_ on table TABLE) (Session: )

You might get this error if you enter in a duplicate value in your primary key column. Seems pretty standard and fair enough. But what about when you’re not?

This error can occur when you have an IDENTITY primary key column, that you have entered values into.

For example my table looked something like this:

CREATE TABLE DIM_TBL_STORE (
STOREID DECIMAL(18,0) IDENTITY NOT NULL ENABLE,
STORENAME VARCHAR(250) UTF8);
ALTER TABLE DIM_TBL_STORE ADD CONSTRAINT PK_DIM_TBL_STORE PRIMARY KEY(STOREID) ENABLE;

We can then check the value of the identity column; COLUMN_IDENTITY returns 0 as expected. All good.

select * from SYS.EXA_ALL_COLUMNS
where Column_schema = 'ROSE3'
AND COLUMN_TABLE = 'DIM_TBL_STORE'
AND COLUMN_NAME = 'STOREID';

Let’s then add some core stores with set IDs :

INSERT INTO DIM_TBL_STORE
SELECT 1, "StoreA"
UNION SELECT 2, "StoreB"
UNION SELECT 3, "StoreC"

SELECT * FROM DIM_TBL_STORE

This shows the 3 rows as expected.

What is unexpected is that the identity value is still zero. You can check this by running the exa_all_columns query, or looking at the info for the table in exaplus.

IdentityColumnWithZero

So next, let’s add a new store, this time not specifying a value for storeid.

INSERT INTO DIM_TBL_STORE (STORENAME)
SELECT 'STORED';

From this we get the error

Error: [27002] constraint violation - primary key (PK_ on table TABLE) (Session: )

Oops. Now we can’t enter any data, because the identity value being offered up by the table is 1. But 1 is taken! So, in fact, are 2 and 3. We need value 4.

To do this, the Identity value needs to be reset to a value after the max identity of the table.

ALTER TABLE DIM_TBL_STORE
ALTER COLUMN storeid SET IDENTITY 4;

Checking the exa_all_columns table, you can now see the value being 4.

The insert will now succeed.

In SQL server, we would have got around this by toggling IDENTITY_INSERT on or off, and SQL Server would have dealt with securing us the next unique value. With Exasol we’ve got to manage it a bit more.