Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2010
    Posts
    30

    Unanswered: Could anyone please tell me how to correctly create a table in MySQL?

    Hi there,

    I'm using MySQL Version 5.1.41 from XAMPP and it's really bugging me because I can't create a table.
    (If it was in Oracle, it probably had been created by now or at least, better error msg and reference manual would have eased my pain.)

    Code:
    CREATE TABLE 	user
    (userid		NUMBER	AUTO_INCREMENT,
    username	VARCHAR(255),
    password	VARCHAR(255),
    isadmin		INT,
    PRIMARY KEY	(userid));
    I understand I'm a noob alright but seriously, I don't know what to modify.
    I checked the reference manaual as well ( MySQL :: MySQL 5.1 Reference Manual :: 12.1.17 CREATE TABLE Syntax ) and maybe it's a lanugage problem but for the life of me, I can't understand what it meant!
    One has to wonder why they didn't include any example.

    I'm sorry if my post sounds quite negative but I find it extremely difficult to understand MySQL, sometimes. Hence, I'm so frustrated.

    Anyway, please help me resolve this issue. Thanks in advance.

    Best regards,
    db_newbie

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    NUMBER is not a valid data type in MySQL
    The valid types are listed on the page you have linked to

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can either do it through DDL directly in the MySQL command line

    or you can run scripts containing the DDL

    or you can use a front end tools such as Heidi SQL, MySQL Adminsitrator (or MySQL Query Bench), or any of the modelling tools.

    many modelling tools require you to buy the tool to talk directly to the db, but most will generate the DDL that you can copy and paste into a tool that will directly run SQL.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by shammat View Post
    NUMBER is not a valid data type in MySQL
    The valid types are listed on the page you have linked to
    Ahh, I see.
    Thank you for that. So, I should be able to use the data type "NUMERIC", right?

    Also, I don't think there's anything wrong with the syntax, is there?

    Cheers and regards,
    db_newbie

  5. #5
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by healdem View Post
    you can either do it through DDL directly in the MySQL command line

    or you can run scripts containing the DDL

    or you can use a front end tools such as Heidi SQL, MySQL Adminsitrator (or MySQL Query Bench), or any of the modelling tools.

    many modelling tools require you to buy the tool to talk directly to the db, but most will generate the DDL that you can copy and paste into a tool that will directly run SQL.
    Oh ok. So, what you mean is..there are tools, softwares that will take in Oracle SQL (or any other SQL) and interpret them in MySQL compatible syntax, right?
    That's something new for me. Thanks

    Regards,
    db_newbie

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by db_newbie View Post
    Ahh, I see.
    Thank you for that. So, I should be able to use the data type "NUMERIC", right?
    For an autoincrement column I would use integer or even bigint depending on how many rows you expect.

    Also, I don't think there's anything wrong with the syntax, is there?
    As far as I can see, no (apart from the wrong datatype)

    Next time remember to post the error message as well

  7. #7
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by shammat View Post
    For an autoincrement column I would use integer or even bigint depending on how many rows you expect.


    As far as I can see, no (apart from the wrong datatype)

    Next time remember to post the error message as well
    Yes, I'll remember that Was just too frustrated to think straight.

    There aren't going to be that many rows, I think 2 digits would be enough.

    In fact, this is a University assignment mainly for Web so I won't have to do much in MySQL (thankfully) apart from setting up the tables.

    Thanks for all the replies, and cheers shammat.
    You've helped me out, yet again

    Regards,
    db_newbie

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    2 digits = TINYINT

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

  9. #9
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by r937 View Post
    2 digits = TINYINT

    Does it mean I have to do like this
    Code:
    NUMBER TINYINT
    instead of
    Code:
    NUMBER INT(2)
    ?

    They're both are the same though, right?

    Cheers and regards,
    db_newbie

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by db_newbie View Post
    They're both are the same though, right?
    no, they are not

    TINYINT holds numbers from -128 to 127 (if UNSIGNED, from 0 to 255)

    INTEGER holds numbers from -2147483648 to 2147483647 (if UNSIGNED, from 0 to 4294967295)

    the number in parentheses, like INT(2), does not do what you think it does

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

  11. #11
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try just:

    Code:
    CREATE TABLE test
    (id TINYINT AUTO_INCREMENT,
    ...
    );
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I would never use tinyint for a primary key column, not even for a university project.

    The very marginal space savings do not justify the major problems that arise when you indeed want to insert more than 127 rows (that is including deleted rows).

    So I would simply stick to integer

  13. #13
    Join Date
    Nov 2010
    Posts
    30
    Thanks for all your suggestions and I really wish MySQL Reference Manual was written by people in this forum. Would had been a lot easier to read cause I always get a "straight answer"

    My lecturer also uses INT for the previous tables created. So, I'll probably stick with that. I'm sure you have to save disck space irl but atm, I don't have to worry about that. Since, this is a web dev assignment, I won't be inserting many rows either.

    Although, hat TINYINT thing is pretty cool. Haven't heard of it before.

    Thank you.

    Regards,
    db_newbie

  14. #14
    Join Date
    Nov 2010
    Posts
    30
    Actually, NVM

    It was my mistake; an extra bracket.

    Thank you all
    Last edited by db_newbie; 01-27-11 at 11:49.

Posting Permissions

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