Constraint violation when merging into table

Question

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);

sorry i chose wrong. this is Exasol, which is 95% similar to ORacle — Peter Pik, Apr 18 '17 at 18:27
That 5% might be vital. But, you've shown the PK for the staging table and not for the target table - so which column(s) form the PK on that table? — Alex Poole, Apr 18 '17 at 18:42
Let's say the merge statement did not find a match and it found two rows in ```STGQUALIFYING``` with ```[RACEID, DRIVERID]```=>[1, 1], [1, 2], this can exist in stage because the combo is the primary key. This would violate the primary key constraint in ```QUALIFYING``` because you are trying to insert duplicate records ```RACEID=1```. — Anand, Apr 18 '17 at 18:55
Yes, that PK just looks wrong; presumably it was either supposed to be on the`raceid,driverid` combination, or it was supposed to be on `qualifyid` (maybe with a UK on the other two columns). — Alex Poole, Apr 18 '17 at 19:28
Then you seem to have manually set some qualifyid values, and the auto increment is now causing duplicates. — Alex Poole, Apr 18 '17 at 19:30
qualifyid is auto_increment and it should just automatically create a new record or update based on raceid and driverid? — Peter Pik, Apr 18 '17 at 19:33
Unrelated, but: please use proper value literals. `'0'` is a string value not a number. Assigning that to an integer column (as a default) is bad coding style - `0` is a number — , Apr 18 '17 at 19:49

score 1 · Accepted Answer · answered Apr 18 '17 at 19:46

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.

Constraint violation when merging into table

1 Answers1