SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Auto increment with phpMyAdmin.

    I have a table set up with a filed called primary. It is not presently set up for auto-increment. Can I set it up for auto-increment now by using phpMyAdmin? and if so, how. I can't find any provisions in phpMyAdmin for doing this.

    I am hoping I can do this without destroying the data already in the database.

    Lawrence

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,053
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)
    You can ALTER tables using phpmyadmin. Log in, select the database, select the table, select the field, and then click change (the pencil icon).

    Every table I have ever created has had an auto-increment id field, so I don't know what would happen if you made a field auto-increment after it had rows in it already. AFAIK it depends on the highest value in the field. but generally will start where the last left off. i.e. if the last id was 157, the next (the first auto-increment) would be 158 irregardless if rows 0 to 20 had been deleted (in other words it wouldn't start at 0 to fill in the lower numbers).

    It might be a good idea to create a test clone of the table and experiment with that first.

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    From the structure tab you can set whether a field in autoincrement or not.

    From the operations tab you can change the current value of the autoincrement.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Every table I have ever created has had an auto-increment id field...
    not picking on you or anything, because i know many people do this, but it is often unnecessary and sometimes even counter productive

    do you routinely, as frequently as assigning an auto_increment, also declare a UNIQUE constraint?

    if not, then "ur doing it wrong"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Very few of the tables I have ever created have ever needed an autoincrement field. Usually there is an obvious field or combination of fields that qualifies to be used for the primary key without needing to invent extra fields. It is often a generated value such as a product code but something that still has meaning beyond providing a unique value for the key - usually being used outside of the database as well as in.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,053
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)
    Honestly no. In fact the only things I've ever UNIQUEd are fields like username, email, etc, where I didn't want duplicates. I guess I've always figured that auto-increment took care of that auto-magically. But it does make a lot of sense, I certainly wouldn't want duplicate id values either. Even if I use them almost exclusively for my benefit and don't use them for table-to-table relationships or in any "live" code (basically just to "tag" the rows). Although my database work is still advanced-newbie-ish at best, I've gotten somewhat better than my "monkey see, monkey do" days, but not much. I'm getting to understand how databases "work" some and I'm getting away from the simple "one table with everything in it" way of thinking, but I've still got a way to go before I attempt writing any complex architecture using relational tables, keys, joins etc.

    You may be correct in saying I'm not alone in doing it wrong, but having company doesn't make it right. Thanks for pointing it out. I don't want to carry any bad practices along if I ever start writing databases where my inefficiencies will start to matter more and more.

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Very few of the tables I have ever created have ever needed an autoincrement field. Usually there is an obvious field or combination of fields that qualifies to be used for the primary key without needing to invent extra fields. It is often a generated value such as a product code but something that still has meaning beyond providing a unique value for the key - usually being used outside of the database as well as in.
    Are you saying I don't need an index field unless I use it external to the database? I have read where I should have one and that it should be auto incremented. If this is not needed for the proper operation of the database beyond my normal data I don't want to use it.

    My data base is basically set up for reservations for a new company. They plan on setting up for four reservations a day. I am basing all my data on the reservation date field. When a potential customer wants to make a reservation I will check to see how many of the same date are listed in the reservation date field. If there are less than 4, I will allow them to make a reservation. Besides this date, the fields are first name, last name, address, city, state, zip, phone, email, and a few more specific to the reservation they want to make.

    I want to keep the database as simple as possible.

    So far, I have a field I called primary, but I could not find a way to make it auto increment. I have populated the database with test data. If I don't need this field I will just remove it.

    I'm sure it is obvious by now, this is my first database application.

    Lawrence

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    If there is already a field in the table that will always have a unique value and which doesn't need to be updated after the record is created then that can be used as the primary key. It is only when there is no unique valued field in the table or where you need that field to be easily updateable that you need to resort to creating a meaningless field to use as the primary key. Even then you only need it if it is going to be referenced from some other table.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    everything stephen has said so far is accurate, but in the particular case of people, it's ridiculously difficult to find a column or combination of columns that will reliably have unique values for people, so a surrogate key is almost always used

    but here, it appears that the table is not about people, but about reservations

    from the description of your situation, the primary key would be date + number (1-4)

    tip: don't call it "primary" because that's a reserved word

    p.s. stephen, a primary key can be updated quite easily, even if it is referenced by foreign keys (ON UPDATE CASCADE takes care of keeping them in sync)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Stephen and r937,

    I am slowly beginning to understand.

    I still have some questions in my mind about the key. Somewhere in phpMyAdmin, I read where it should be called "primary, and only primary". I tried this morning to find where I saw this, but I can't find it again.

    Is it absolutely necessary for me to have a field with a unique value? If so why? Is it something MySql needs? I can't see where I, for my own use, need a unique value in any field.

    If I need one, I can easily do as r937 mentioned about adding a number (1-4) to the date, but then do I need to do something else to indicate this is the primary key?

    Where does one learn the finer details about this. Is there a on-line reference, or a book suitable for beginners?

    One last question. If I ever need it, where under the structure tab do I find the way to auto-increment? I have spent hours trying to find this. The only reference I see to auto-increment is when you are going to move or copy a table.

    Thanks again for you input on this.

    Lawrence

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there will be a book about SQL suitable for beginners announced by sitepoint in early january

    i would advise you not to use the "wysiwyg" panels of phpmyadmin until you have a complete understanding of how SQL works and how tables are designed

    for the interim, do everything in the SQL window

    for example,
    Code:
    CREATE TABLE reservations
    ( res_date DATE NOT NULL
    , res_no TINYINT NOT NULL
    , PRIMARY KEY ( res_date, res_no )
    , lastname VARCHAR(50) NULL
    , firstname VARCHAR(50) NOT NULL
    , ...
    );
    and yes, at this point in your development, it is vital that you have a unique column (or set of columns) in every table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2005
    Location
    Southern Nevada, USA
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there will be a book about SQL suitable for beginners announced by sitepoint in early january

    i would advise you not to use the "wysiwyg" panels of phpmyadmin until you have a complete understanding of how SQL works and how tables are designed

    for the interim, do everything in the SQL window

    for example,
    Code:
    CREATE TABLE reservations
    ( res_date DATE NOT NULL
    , res_no TINYINT NOT NULL
    , PRIMARY KEY ( res_date, res_no )
    , lastname VARCHAR(50) NULL
    , firstname VARCHAR(50) NOT NULL
    , ...
    );
    and yes, at this point in your development, it is vital that you have a unique column (or set of columns) in every table
    Thank you. I will check out the book, and follow your advice.

    Lawrence

  13. #13
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    p.s. stephen, a primary key can be updated quite easily, even if it is referenced by foreign keys (ON UPDATE CASCADE takes care of keeping them in sync)
    I was thinking more in terms of its use outside of the database and that changing it in the database would mean that any other references to it would be invalidated (such as on the printed reservation information in this particular situation where it would be the obvious value to print on all the paperwork that is produced so as to make lookups of the database when the paperwork is available much easier).
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •