I have a view in an external database (MySQL) that I would like to map to an external rollbase object.
This view does not have a unique ID. The only unique thing it has is the concatenation of 3 fields. So I added another column in the view for this 'ID', to have a unique identifier.
Now to map this view in RB, I use this ID as primary key, and it is not working : The object is built correctly in RB, but not populated with data from that view.
Also, this ID is a concat of an INTEGER, a GUID and a DATE. Is that an authorized format for primary key ? If not do you have any other suggestions? Because obviously we can not have an auto number in a view to act as a unique ID ..
I just came across this : '' 6. If desired, specify a single numeric unique column as the table's primary key." in the documentation here :
So I assume the primary key has to be a NUMERIC UNIQUE Column. Can you confirm please?
So to work around this, I am trying to use a composite key, by selecting two columns as primary key (instead of the concat). It is still not working.
Are they any constraints on the type of the columns we can use in the composite key ?
I am now using two integer fields.
Any ideas why this is not working please?
I have created a sample view with three int columns ,pulled this object to RB and data displayed correctly. But when I created a view using three columns(one of them is concatenation of third column) object created successfully but data is not getting visible. I will check with our internal teams and will let you know.
However,as a workaround can you please alter your table by updating column name with combination of other three fields and create a view again?
What do you mean by :
"as a workaround can you please alter your table by updating column name with combination of other three fields and create a view again" ?
Change the name of the column ?
Also in your first test : "I have created a sample view with three int columns ,pulled this object to RB and data displayed correctly.", What are you using as primary key while mapping the view in RB ?
As on date we cannot have composite key(in your case it is ID) as a PRIMARY KEY in rollbase which is why data is not getting populated in RB.
Can you please try creating object in RB without assigning composite key as PK instead give a separate column and try again?
So I tried your suggestion, I am mapping the view to an external RB object using a separate column as Primary Key. But I dont have any unique column in that view, that is why I did the concat initially to have something unique.
It is not working. But this makes sense because this column I am using is not unique in the View.
Any other suggestions please?
Also, are you saying we can not have composite keys for now ?
Is that going to be allowed anytime soon ?
In your case you were trying to use "ID" as a primary key in RB ,making it a mandatory field while creating the record. But as per your scenario it should be the combination of other columns which eventually leads to data inconsistency in DB. I would suggest you not to make "ID" as a PK in RB .
Yes I got that. But how do I solve the problem then ? I need a primary key in RB. And none of the columns I have in that view is unique so none can act as primary key.
You can select multiple columns as primary key. Select combination of columns which make unique key for your case then this will work.
Thanks and Regards
What should be the type of those columns ?
I am using two columns to build the composite key. One is an Integer, and one is a Text(36).
The object is built, but not populated with data.
Again, this is a VIEW, not a table.
What could be wrong here ?
To clear the confusion please see the below steps which I performed:
1)Created table with two columns and made both of them as composite key as follow:
create table abc(name varchar(20), rno int,primary key(name,rno));
I have inserted few records in the above table.
2)Now I have created view as follows:
create view abcview as select * from abc;
Now after pulling this object into RB I have assigned name , rno as primary keys and data gets populated without any issue.
From my understanding you were creating view by concatenating columns as below:
create view abcview as select name,rno,concat(name,rno) from abc; from which you cannot be able to create concat(name,rno) as PRIMARY KEY.
Thanks for the clarifications.
The issue was not the composite key, it was the name of one of the columns that was containing a '('. As soon as I renamed it, the mapping with the same composite key worked fine.
This is an interesting thing people need to know in my point of view. I presume there are some restrictions on the column names of the external tables that can be mapped to external objects in RB, probably no specific characters like '(' or ')' or any others..
I think this should be specified in the documentation.