Results 1 to 7 of 7

Thread: Back Again!

  1. #1
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20

    Unanswered: Back Again!

    Hi,

    I'm back again (yes, that soon!).

    I need to call on your help again, if I may.

    I've had an eventful evening thus far. I have installed MySQL and the GUI tools, and it wasn't plain sailing as I was actually downgrading the version from 6 to 5.1, the current version. There was a slight problem when it came to trying to get the 'MySQL' service to run on start-up as a service by the same name was already installed and to resolve it I had to delete it from the command-line before rebooting my PC.

    And once logged into MySQL I had to figure out why it wasn't possible to create a table named 'test'; I received an error message that said 'the database already exists'. Confused me at first as I hadn't created it, which led me to believe that a database with that name must come installed with MySQL by default - is that right? To see if that was indeed the answer I used the MySQL Administrator tool for the very first time. Most impressive! And there the table was.

    I am normally the impatient type that likes everything to run smoothly without any setbacks. But I won't get far in this business without embracing challenges like the above, and I've got to say the troubleshooting has actually been the thing I've enjoyed most so far.

    Anyway, I'm rattering on. Let me now get to the point.

    I've created a database named 'facts', and a table using the syntax below:

    Code:
    CREATE TABLE people
    (
    person_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(20) NOT NULL,
    lname VARCHAR(20) NOT NULL
    );
    ... and the one clause that I forgot to include was DEFAULT. Please correct me if my line of thinking is wrong, but with the above syntax wouldn't the default value be NULL resulting in the first row that gets inserted having a person_id of 0, when I'd like it to be 1? Or is SQL not like most other programming languages, and counts from 1 rather than 0?

    Rather than dropping the table and re-creating it (which I've considered but that's not the way to learn), I'd like to update it. I'm not sure how to do this and the book I'm currently reading doesn't go into much details about table statements as they're more pertinent to DBA's and this book is targeted at programmers.

    I hazzared a guess - and it was just that - and this was what I came up with:

    Code:
    ALTER TABLE people 
    ALTER person_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT CONSTRAINT DEFAULT (0);
    ... and surprise, surprise, it returned an error.

    Can you help? I don't necessarily want you to post the correct code. But any pointers would be much appreciated.

    Alex

    P.S - I love this place already.
    Last edited by Alex Wright; 02-27-09 at 03:57.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no default for an auto_increment

    ("these aren't the droids you're looking for")



    all your INSERT statements should make no mention of the id at all

    INSERT INTO people VALUES
    ( 'Joe', 'Blow') , ( 'Todd' , 'Kamalfeszchuk' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    Thanks r937,

    Does that mean the first record to be inserted into the table will have an ID of 1? If not, how can this be achieved?

    Alex

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Alex Wright
    Does that mean the first record to be inserted into the table will have an ID of 1? If not, how can this be achieved?
    Why does the value matter? The only property that I demand of an AUTO_INCREMENT column is that the values be unique. Expecting the values in the column to have specific values like 0 or 1, or even be sequential (no gaps) is just asking for trouble. In some cases, you can't even depend on the values being ascending (such as when you roll over from the max to the min value for a given datatype).

    If you want to save yourself a lot of grief, I'd strongly suggest that you forget any assumptions about AUTO_INCREMENT columns other than uniqueness of the values.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Alex Wright
    Does that mean the first record to be inserted into the table will have an ID of 1?
    yes

    but really, you should try it -- you'll answer these types of questions with much more satisfaction that way

    and if you'd like to ask further questions about mysql, let's do so in the mysql forum


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    Quote Originally Posted by Pat Phelan
    Why does the value matter? The only property that I demand of an AUTO_INCREMENT column is that the values be unique. Expecting the values in the column to have specific values like 0 or 1, or even be sequential (no gaps) is just asking for trouble. In some cases, you can't even depend on the values being ascending (such as when you roll over from the max to the min value for a given datatype).

    If you want to save yourself a lot of grief, I'd strongly suggest that you forget any assumptions about AUTO_INCREMENT columns other than uniqueness of the values.

    -PatP
    OK, thanks for clearing that up for me Pat.

  7. #7
    Join Date
    Feb 2009
    Location
    Bournemouth, Dorset
    Posts
    20
    Quote Originally Posted by r937
    yes

    but really, you should try it -- you'll answer these types of questions with much more satisfaction that way

    and if you'd like to ask further questions about mysql, let's do so in the mysql forum


    Ooops! Sorry, my bad.

Posting Permissions

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