Primary Key Semantic

[saniboy.788]

Hi,

I defined a relational view (called Student(Id,Name,Surname,Age)) which unifies 2 different sources (called Student_source_1(Id,Name,Surname,Age) and Student_source_2(Id,Name,Surname,Age)).

One column of Student is a Primary Key for Student , let say "Id".

Now

in the Student_source_1 I have the tuple (2,John,Snow,24)

in the Student_source_2 I have the tuple (2,John,Snow,25)

The query SELECT * FROM Student gives me both John Snow.

I have tried to modify the age in both sources in order to give the same  age to John Snow and still the query engine gives both John Snow.

How can I define a real unification of the data sources?

How can I define a Primary Key?

Thanks

[shawkins]

> 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

[saniboy.788]

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

[shawkins]

I'm not sure I follow your question.  What theoretical infromation are you looking for?

Steve

[saniboy.788]

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

[shawkins]

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.