DB composite key and add column on table

[anguslou]

I am new to DB nodes, and want to setup database to save stock info, eg, ticker, date, open, close, etc…

Firstly I tried SQLite node, which doesn’t update properly, then I tried H2 and MS Access. They work but I get some other questions.

1. How to set composite key, eg, ticker + date? so that duplicate ticker + date should not be able to update on the same table. I was not able to do that in “Keys” tab of DB table creator. Either I can only set one attribute as key, or no key is set so duplicate record can be updated.

2. Is it possible to add columns after the DB is created? eg, if I want to add a “SMA” column on the table? I tried with DB update node but cannot update if there is no SMA column on the original table.

3. Which DB is recommend? SQLite doesn’t work well, and I can’t work around with H2 and MS Access with above issues.

[bruno29a]

Hi @anguslou , it sounds like you are new to Database altogether…

Most database systems support unique composite keys - to take the systems you have tried, SQLite, H2 and MS Access all do.

Regarding your questions:

@anguslou

How to set composite key, eg, ticker + date? so that duplicate ticker + date should not be able to update on the same table. I was not able to do that in “Keys” tab of DB table creator. Either I can only set one attribute as key, or no key is set so duplicate record can be updated.

With a unique composite key constraint, the system will not allow you to have duplicate keys, so it will not allow you to INSERT duplicates. UPDATE can still happen, but you cannot update the keys to existing ones

@anguslou

Is it possible to add columns after the DB is created? eg, if I want to add a “SMA” column on the table? I tried with DB update node but cannot update if there is no SMA column on the original table.

So here, you want to add columns after the TABLE is created, not the DB. And DB Update node is to update the data of a table, not the structure. For adding the column, see General comments below.

@anguslou

Which DB is recommend? SQLite doesn’t work well, and I can’t work around with H2 and MS Access with above issues.

This is hard answer. Which system/OS do you want the DB server to run on? Any budget constraint? What kind of operations will you be doing?

General comments:
Regardless of what DB you choose, there are some DB operations that may not be available from Knime nodes, however, Knime does provide a DB SQL Executor node where you can basically do any DB operations via SQL commands.

For example, if you want to add a SMA column after the table is created, you can execute an ALTER TABLE ADD COLUMN via the DB SQL Executor if you want. However, design-wise, you would want to have this done outside of your workflow, unless you intend to run that workflow only once - it would try to add the column every time you run the workflow.

Similarly for Keys, if you can’t define the with DB Table Creator, you can always add them with the DB SQL Executor