5 Replies Latest reply on Jun 26, 2013 8:34 PM by Steven Hawkins

    Primary Key Semantic

    Luigi Asprino Newbie

      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

        • 1. Re: Primary Key Semantic
          Steven Hawkins Master

          > 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

          1 of 1 people found this helpful
          • 2. Re: Primary Key Semantic
            Luigi Asprino Newbie

            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
              Steven Hawkins Master

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

               

              Steve

              • 4. Re: Primary Key Semantic
                Luigi Asprino Newbie

                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
                  Steven Hawkins Master

                  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.