Search code examples
sqlexasolution

Constraint violation when merging into table


I'm having a staging table and a datawarehouse table, which keep giving me constraint violation. i can't seem to figure out why since DRIVERID and RACEID a combination of those should be unique? How come i get contraint violation - primary key

table

CREATE TABLE QUALIFYING (
QUALIFYID      DECIMAL(18,0) IDENTITY NOT NULL,
RACEID         DECIMAL(18,0) DEFAULT '0' NOT NULL,
DRIVERID       DECIMAL(18,0) DEFAULT '0' NOT NULL,
CONSTRUCTORID  DECIMAL(18,0) DEFAULT '0' NOT NULL,
DRIVERNUMBER   DECIMAL(18,0) DEFAULT '0' NOT NULL,
DRIVERPOSITION DECIMAL(18,0) DEFAULT NULL,
Q1             VARCHAR(255) UTF8 DEFAULT NULL,
Q2             VARCHAR(255) UTF8 DEFAULT NULL,
Q3             VARCHAR(255) UTF8 DEFAULT NULL,
PRIMARY KEY(QUALIFYID)
);

Staging

CREATE OR REPLACE TABLE STGQUALIFYING(
      raceId int DEFAULT '0' NOT NULL,
      driverId int DEFAULT '0' NOT NULL,
      constructorId int DEFAULT '0' NOT NULL,
      driverNumber int DEFAULT '0' NOT NULL,
      driverPosition int DEFAULT NULL,
      q1 varchar(255) DEFAULT NULL,
      q2 varchar(255) DEFAULT NULL,
      q3 varchar(255) DEFAULT NULL,
      PRIMARY KEY(RACEID, DRIVERID)
);

SQL

MERGE INTO QUALIFYING c
USING STGQUALIFYING n
ON
(n.RACEID = c.RACEID AND n.DRIVERID = c.DRIVERID)
WHEN MATCHED THEN
    UPDATE SET
    CONSTRUCTORID = n.CONSTRUCTORID, DRIVERNUMBER = n.DRIVERNUMBER, DRIVERPOSITION = n.DRIVERPOSITION, Q1 = n.Q1, Q2 = n.Q2, Q3 = n.Q3
WHEN NOT MATCHED THEN
    INSERT (RACEID, DRIVERID, CONSTRUCTORID, DRIVERNUMBER, DRIVERPOSITION, Q1, Q2, Q3) VALUES
    (RACEID, DRIVERID, CONSTRUCTORID, DRIVERNUMBER, DRIVERPOSITION, Q1, Q2, Q3);

Solution

  • The EXASolution user manual says:

    The content of an identity column applies to the following rules:

    • If you specify an explicit value for the identity column while inserting a row, then this value is inserted.
    • In all other cases monotonically increasing numbers are generated by the system, but gaps can occur between the numbers.

    and

    You should not mistake an identity column with a constraint, i.e. identity columns do not guarantee unique values. But the values are unique as long as values are inserted only implicitly and are not changed manually.

    You've put a primary key constraint on your identity column, so it must be unique. Since you are getting duplicates from your merge, either (a) you have, at some point, provided explicit values as in the first bullet above or updated a value manually, and the monotonically increasing sequence has reached a point where it is clashing with those existing values; or (b) there's a bug in their merge. The former seems more likely.

    You can look at recently inserted value if you have one, or do a temporary insert of a new row (with merge) to see if it will create a row successfully, and if so whether you already have ID values higher than the one it allocates for that new row. If there are no higher values already, and insert works and merge continues to fail consistently, then it sounds like something you'd need to raise with EXASolution.