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.
dBforums
>
Back Again!
02-26-09, 15:21
Linear Mode
