Loading ...
Sorry, an error occurred while loading the content.
 

Primary keys

Expand Messages
  • Andreas Aderhold
    Hi, I choosed metabase for a upcoming (big) project as db-abstraction (maybe in conjunction with binarycloud r2). So far, I worked with phplib db.class.
    Message 1 of 9 , Jun 13 9:44 AM
      Hi,

      I choosed metabase for a upcoming (big) project as db-abstraction
      (maybe in conjunction with binarycloud r2). So far, I worked with phplib
      db.class. Metabase is cooler, execpt ones: Primary keys.

      When is it going to support primary keys? Every common DBMS does support
      primary keys. For the DBMS that do not support them, just ignoring would be
      nice (like you do with the UNSIGNED propery).

      It would be much more convienient (for me) to define the pks in the schema
      file rather than applying a mysql/oracle/pgsql/whateverDBMS beauty patch.


      Andi
    • Manuel Lemos
      Hello Andreas, ... Well, in practice primary keys are just unique indexes, except that you can only have one primary key per table and you can have more than
      Message 2 of 9 , Jun 14 9:25 PM
        Hello Andreas,

        On 13-Jun-01 13:44:55, you wrote:

        >I choosed metabase for a upcoming (big) project as db-abstraction
        >(maybe in conjunction with binarycloud r2). So far, I worked with phplib
        >db.class. Metabase is cooler, execpt ones: Primary keys.

        >When is it going to support primary keys? Every common DBMS does support
        >primary keys. For the DBMS that do not support them, just ignoring would be
        >nice (like you do with the UNSIGNED propery).

        >It would be much more convienient (for me) to define the pks in the schema
        >file rather than applying a mysql/oracle/pgsql/whateverDBMS beauty patch.

        Well, in practice primary keys are just unique indexes, except that you can
        only have one primary key per table and you can have more than one unique
        index.

        OTHO, although you don't need primary keys as long as you can specify
        unique indexes in the schema, I don't disagree that Metabase should support
        somehow primary and foreign keys.

        I can't promise anything just yet, because I have a major project going on
        besides Metabase, but as soon as I get some time for Metabase, that is on
        my todo list, if somebody else does not add that support before me.



        Regards,
        Manuel Lemos

        Web Programming Components using PHP Classes.
        Look at: http://phpclasses.UpperDesign.com/?user=mlemos@...
        --
        E-mail: mlemos@...
        URL: http://www.mlemos.e-na.net/
        PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
        --
      • Andreas Aderhold
        Hi Manuel, ... can ... That s right. But within mysql there are more primary keys per table possible and the only eficient usage I have for PKs is in
        Message 3 of 9 , Jun 15 5:21 AM
          Hi Manuel,

          > Well, in practice primary keys are just unique indexes, except that you
          can
          > only have one primary key per table and you can have more than one unique
          > index.
          That's right. But within mysql there are more primary keys per table
          possible and the only "eficient" usage I have for PKs is in link-tables
          solving n:m relations . E.g.:

          Table 1: locations (locationId, ....)
          Table 2: extras (extraId, ...)
          Table 3:

          locationextras
          -----------------
          locationId, pk
          extraId, pk

          This assures that each row (each pair of values) of the table is unique (in
          Mysql). Ok, if the application is well designed duplicate rows should not
          occur ;-) But they might..
          Defining unique indexes on this fields would not solve the problem, caus
          every value has to be unique in the column.

          > besides Metabase, but as soon as I get some time for Metabase, that is on
          > my todo list, if somebody else does not add that support before me.
          cool! :-)


          Andi
        • Manuel Lemos
          Hello Andreas, ... No, like primary keys, unique indexes may be made of multiple columns. It is just the combination of values of all columns that has to be
          Message 4 of 9 , Jun 15 3:24 PM
            Hello Andreas,

            On 15-Jun-01 09:21:08, you wrote:

            >> Well, in practice primary keys are just unique indexes, except that you
            >can
            >> only have one primary key per table and you can have more than one unique
            >> index.
            >That's right. But within mysql there are more primary keys per table
            >possible and the only "eficient" usage I have for PKs is in link-tables
            >solving n:m relations . E.g.:

            >Table 1: locations (locationId, ....)
            >Table 2: extras (extraId, ...)
            >Table 3:

            > locationextras
            > -----------------
            > locationId, pk
            > extraId, pk

            >This assures that each row (each pair of values) of the table is unique (in
            >Mysql). Ok, if the application is well designed duplicate rows should not
            >occur ;-) But they might..
            >Defining unique indexes on this fields would not solve the problem, caus
            >every value has to be unique in the column.

            No, like primary keys, unique indexes may be made of multiple columns. It
            is just the combination of values of all columns that has to be unique. It
            is the same thing for primary indexes.

            For what you intend, using unique indexes is the same as defining primary
            keys because the server will always try to define the available indexes to
            optimize the searches of the queries to avoid full table scans. That works
            that way regardless if you query is a join or not. Try using the SQL
            command EXPLAIN to see if a given query takes advantages of any indexes or
            not.


            Regards,
            Manuel Lemos

            Web Programming Components using PHP Classes.
            Look at: http://phpclasses.UpperDesign.com/?user=mlemos@...
            --
            E-mail: mlemos@...
            URL: http://www.mlemos.e-na.net/
            PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
            --
          • Andreas Aderhold
            Hi Manuel, ... It ... works ... Hmm. That is how I did it first. Declaring unique indexes on both fields of the link-table , resulting in the following mysql
            Message 5 of 9 , Jun 16 5:22 AM
              Hi Manuel,

              > No, like primary keys, unique indexes may be made of multiple columns. It
              > is just the combination of values of all columns that has to be unique.
              It
              > is the same thing for primary indexes.

              > For what you intend, using unique indexes is the same as defining primary
              > keys because the server will always try to define the available indexes to
              > optimize the searches of the queries to avoid full table scans. That
              works
              > that way regardless if you query is a join or not. Try using the SQL
              > command EXPLAIN to see if a given query takes advantages of any indexes or

              Hmm. That is how I did it first. Declaring unique indexes on both fields of
              the "link-table", resulting in the following mysql table after installing
              the schema:

              CREATE TABLE locationextras (
              locationId int(11) DEFAULT '0' NOT NULL,
              extraId int(11) DEFAULT '0' NOT NULL,
              UNIQUE extraId (extraId),
              UNIQUE locationId (locationId)
              );

              But by inserting data in the table dmbs issues an error if a field value is
              dumplicate. E.g.:

              locationId extraId
              1 1
              1 2

              This does not happen if both fields are primary keys. The dbms only errors
              if a a entry already exitst

              1 1
              1 1 <- duplicate row error if inserted again

              The only reason I prefer the desired behavior is to prevent duplicate rows
              "natively".


              Thanks in advance,
              Andi
            • Manuel Lemos
              Hello Andreas, ... I think you are missing the fact that you can make a single unique index with both fields. That is the same as you want to do with primary
              Message 6 of 9 , Jun 16 10:43 AM
                Hello Andreas,

                On 16-Jun-01 09:22:20, you wrote:

                >Hi Manuel,

                >> No, like primary keys, unique indexes may be made of multiple columns. It
                >> is just the combination of values of all columns that has to be unique.
                >It
                >> is the same thing for primary indexes.

                >> For what you intend, using unique indexes is the same as defining primary
                >> keys because the server will always try to define the available indexes to
                >> optimize the searches of the queries to avoid full table scans. That
                >works
                >> that way regardless if you query is a join or not. Try using the SQL
                >> command EXPLAIN to see if a given query takes advantages of any indexes or

                >Hmm. That is how I did it first. Declaring unique indexes on both fields of
                >the "link-table", resulting in the following mysql table after installing
                >the schema:

                >CREATE TABLE locationextras (
                > locationId int(11) DEFAULT '0' NOT NULL,
                > extraId int(11) DEFAULT '0' NOT NULL,
                > UNIQUE extraId (extraId),
                > UNIQUE locationId (locationId)
                >);

                >But by inserting data in the table dmbs issues an error if a field value is
                >dumplicate. E.g.:

                >locationId extraId
                >1 1
                >1 2

                >This does not happen if both fields are primary keys. The dbms only errors
                >if a a entry already exitst

                >1 1
                >1 1 <- duplicate row error if inserted again

                >The only reason I prefer the desired behavior is to prevent duplicate rows
                >"natively".


                I think you are missing the fact that you can make a single unique index
                with both fields. That is the same as you want to do with primary keys.

                Your index schema description would look something like this:

                <index>
                <name>locationextrasIndex</name>
                <field>
                <name>locationId</name>
                </field>
                <field>
                <name>extraId</name>
                </field>
                </index>



                Regards,
                Manuel Lemos

                Web Programming Components using PHP Classes.
                Look at: http://phpclasses.UpperDesign.com/?user=mlemos@...
                --
                E-mail: mlemos@...
                URL: http://www.mlemos.e-na.net/
                PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
                --
              • Andreas Aderhold
                Hello Manuel, ... Heureka, now I got it. Ok, thats what I wanted to do :)) Thanks a lot for your help and sorry if my posts were a bit off topic for this list.
                Message 7 of 9 , Jun 17 7:51 AM
                  Hello Manuel,

                  > I think you are missing the fact that you can make a single unique index
                  > with both fields. That is the same as you want to do with primary keys.

                  > <index>
                  > <name>locationextrasIndex</name>
                  > <field>
                  > <name>locationId</name>
                  > </field>
                  > <field>
                  > <name>extraId</name>
                  > </field>
                  > </index>

                  Heureka, now I got it. Ok, thats what I wanted to do :)) Thanks a lot for
                  your help and sorry if my posts were a bit off topic for this list.

                  Thanks again,
                  Andi
                Your message has been successfully submitted and would be delivered to recipients shortly.