-
1. Re: Primary Key Semantic
shawkins Jun 26, 2013 7:58 AM (in response to saniboy.788)1 of 1 people found this helpful> How can I define a Primary Key?
From a runtime perspective, a primary key (or any of the other constraints) on a view is a planning aid - and not something that has built-in logic to enforce. It's up to your view logic to perform the necessary consolidation.
> How can I define a real unification of the data sources?
So in your case a union all is too simplistic. If one source is considered the primary and the data is the same, you could use something like:
"select * from Student_source_1 union all (select * from Student_source_2 except select * from Student_source_1)"
or if there are differences in the other other columns you could do something like:
"select id, name, surname, age from (select id, count(id) over (partition by id) id_count, name, surname, age, source from (select id, name, surname, age, 1 as source from Student_source_1 union all select id, name, surname, 2 from Student_source_2) as x) as y where id_count = 1 or source = 1"
Although using window functions may inhibit optimization too much depending upon how you are using the view. Another consideration would be just use a union all with a source column and use a composite key (id, source) for your usage in Teiid.
Steve
-
2. Re: Primary Key Semantic
saniboy.788 Jun 26, 2013 3:44 PM (in response to shawkins)Thank you Steven for your rep.
The first unification is exactly what I did.
I need more theoretical aspects. Where could I find the theory which TEIID implement?
Thanks
Luigi
Steven Hawkins ha scritto:
> How can I define a Primary Key?
From a runtime perspective, a primary key (or any of the other constraints) on a view is a planning aid - and not something that has built-in logic to enforce. It's up to your view logic to perform the necessary consolidation.
> How can I define a real unification of the data sources?
So in your case a union all is too simplistic. If one source is considered the primary and the data is the same, you could use something like:
"select * from Student_source_1 union all (select * from Student_source_2 except select * from Student_source_1)"
or if there are differences in the other other columns you could do something like:
"select id, name, surname, age from (select id, count(id) over (partition by id) id_count, name, surname, age, source from (select id, name, surname, age, 1 as source from Student_source_1 union all select id, name, surname, 2 from Student_source_2) as x) as y where id_count = 1 or source = 1"
Although using window functions may inhibit optimization too much depending upon how you are using the view. Another consideration would be just use a union all with a source column and use a composite key (id, source) for your usage in Teiid.
Steve
-
3. Re: Primary Key Semantic
shawkins Jun 26, 2013 4:37 PM (in response to saniboy.788)I'm not sure I follow your question. What theoretical infromation are you looking for?
Steve
-
4. Re: Primary Key Semantic
saniboy.788 Jun 26, 2013 5:07 PM (in response to shawkins)I need to know how Teiid manage conflicts on the data sources in presence of constraints on views, for instance a Primary Key.
Thanks
Luigi
-
5. Re: Primary Key Semantic
shawkins Jun 26, 2013 8:34 PM (in response to saniboy.788)From a runtime perspective, a primary key (or any of the other constraints) on a view is a planning aid - and not something that has built-in logic to enforce. It's up to your view logic to perform the necessary consolidation.
It would be a new feature to provide pk/fk/unique constraint enforcement in a view layer.