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.