How to deal with tables that doesn't have any primary key
Hello everyone I am new to talend, sorry if I had done something wrong, my question is what to do if we don't have any column in table that can be treated as a primary key or unique identifier, I am having two database (postgresql) tables
1- account
2- account_roles
in account table there are fields like id, name, address, state.. etc and in account_roles table there are only two columns first is account_id which is fk referencing id of account table and second is role (int value ranging from 0-2).
Basically I want to make account_roles table in destination database. by mapping and performing inner_join in two tables
my final table is like -> account_id, role, address, state
I am using tMap to perform innerJoin but the issue is - account_roles tables does not have any column that can be treated as p.k because one account id can have different roles in account_roles table so its not primary key in account_roles table
How to deal with this issue, I also want insert/update functionality in this table. And for making talend to update a table its important to have unique identifier.
Please help me out in this, how can I achieve the desired output in the following scenario.
If you don't have the primary key in the file and you want to update the DB with the data in the file, how can you know which record in the DB to update? You don't have any information about the data already there.
The need to use the account_id as a foreign-key when you do an insert but the account_id might already exist or you might only be creating it now in tMap component.
is the combination of "account Id" AND "role" unique? If you keep adding fields you should get to something that is unique for each record - or else the records themselves would be duplicate...
