SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql basics/primary Key

    Using phpMyAdmin 2.2.0

    I'm simply trying to create a primary key within the table.This is for a job posting site. (two tables, relational database)

    Though when I view the table, the column field will not output unique numbers incrementally for ea row as they should. Only entire "o"'s down the line.

    I have entered. "tinyint, 11, not null, auto increment, primary key "
    (tried "unique" and "index" as well. nothing. note: did not have more than one key at a time)

    Another problem is that DreamweaverMX wants to assign its own "Record" number for the table, (which I can exclude) but I also have the primary number on the table, then also a third ID number intended for a relational database.

    1) How do I get the primary key to work?

    2) What happens when individual rows are deleted in the future from the table. Will primary numbers retain their "old value" . Will MY SQL omit the numbers, reindexing all the rows or preserve the original numbers?

    3) How can I start the primary number sequence at a later number, say "400".(aside from 399 dummy rows above)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    1) primary key is not the same as auto_increment -- you will have to show the structure of your table so that we can troubleshoot it

    2) when rows are deleted, there will be "gaps" in the nice orderly sequence of numbers, but this should make absolutely no difference to you at all

    3) insert a row with that specific value -- i think (haven't tested this myself, don't need to, the actual numbers not meaning anything)

    rudy
    http://r937.com/

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,

    Very much appreciate your response. I have added the primary key later, if this makes a difference but I don't think it does. I've simply added the primary key as the first column. Then a seond ID for joining tables. (joinging job listings to their employer)

    1) this is how Kevin Yank illustrates in "Database Driven Website"...
    "Field Type Null Key Default Extra"
    "Id int(1) Pri 0 auto_inc"

    Reason for this all is I am using the Primary ID as a job number to reference each listing onsite. So I can't change it with edits: row adds/deletes.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you already have a column for joining tables, then make that your primary key, you don't need a separate primary key column

    as a matter of fact, that's the main purpose of a primary key -- joining tables

    (i.e. if you don't need to join a table, ever, then you don't need a primary key)

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2000
    Location
    Chico, Ca
    Posts
    1,125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if you already have a column for joining tables, then make that your primary key, you don't need a separate primary key column

    as a matter of fact, that's the main purpose of a primary key -- joining tables

    (i.e. if you don't need to join a table, ever, then you don't need a primary key)

    I thought Primary Keys provided entry indiviuality ? So if you have two horses named Speedy in the same table/database it won't cause a problem.
    "Happiness doesn't find you, you find happiness" -- Unknown
    www.chuckknows.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    primary keys are, by definition, unique

    it is the unique constraint that provides individuality

    you can declare unique constraints apart from primary keys

    for example, you may assign a primary key of an autonumber to a job candidate table, because firstname/lastname is not an ideal candidate primary key, but you can, in addition, also declare a unique constraint on the email column


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
  •