Can we remove Primary key constraint for a table in Full Load Only task

[vabclb]

We have a full load task and we want to bring incremental load every 24hrs via scheduled task. The task fails if there is an update to the same primary key record. For that we wanted to suppress the primary key constraint on the target table. We tried to remove the constraint via table transformation output window by double clicking the key, but after saving the task its still keeping those primary keys in the output. Is there any way to remove that primary key constraint from full load only task ?

[Dana_Baldwin]

Hi @vabclb 

If you pre-create the target tables without the constraint, then set the option in task settings, full load, full load settings: If target table already exists: from "drop and create table" to "truncate before loading", that should handle it.

Thanks,

Dana

[vabclb]

These are our settings:
1. We want qlik to create initial tables on target. <is there a way that qlik creates target tables without primary key constraints?>

2. In case we remove the target table primary key constraint after initial load, will the task again add constraints as its always reload target and option is "Do Nothing", because we want to capture incremental load

3. What happens to the above setup in case there is a primary key or any schema change on the source?

4. We have our task pulling 24hrs of data everyday and we want to pull incremental load

[Dana_Baldwin · Accepted solution]

Hi @vabclb 

1. No. If Qlik creates the table, and there is a PK column on the source, it will use that on the target. If there is more than one PK column, it will use the first column alphabetically for the PK column.

2. No, the task will not create the constraint as you have it set to "do nothing".

3. Please manually change the DDL on the target tables to match the new structure on the source before running the task with the changes in place on the source.

4. Thanks for clarifying your use case.

Dana

[vabclb]

thanks for clarifying, one follow up question on source schema change:

If task is set to 'Do Nothing" and there is a schema change on the source end, what happens to the task run? will it fail or will it skip the source changes?
Below are the changes we are concerned about:
1. Column removal

2. Column addition

3. Datatype change

How can we handle on the fly schema changes in case we want to go that route? What task changes are required or its not possible?

[Dana_Baldwin · Accepted solution]

Hi @vabclb 

Capturing DDL changes is only possible when the task is set to perform change processing. By default, full load only tasks run a "select *" against the source tables. If the metadata does not match between the source and target tables there would be certainly be errors for removing & adding a column. Most likely for data type changes as well, unless both types are character and the size on the target will still hold the data from the source column. Can the source DBA provide the DDL changes in advance of implementation so you can update the target tables?

[vabclb]

thanks for your inputs. Yeah for most of our sources we do prior schema changes on the target, but for this particular source, DBA's want us to handle schema changes on the fly, as there will be too many changes on daily basis, so we are exploring options. But with full load only task as you mentioned, DDL changes are not captured/applied, so I think I got my answer. Thanks again.