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

db_newbie · #1 (**permalink**) 01-27-11, 06:51

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

shammat · #2 (**permalink**) 01-27-11, 06:59

NUMBER is not a valid data type in MySQL
The valid types are listed on the page you have linked to

healdem · #3 (**permalink**) 01-27-11, 07:00

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.

db_newbie · #4 (**permalink**) 01-27-11, 07:01

Quote:

Originally Posted by shammat

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

db_newbie · #5 (**permalink**) 01-27-11, 07:05

Quote:

Originally Posted by healdem

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

shammat · #6 (**permalink**) 01-27-11, 07:06

Quote:

Originally Posted by db_newbie

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.

Quote:

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

db_newbie · #7 (**permalink**) 01-27-11, 08:36

Quote:

Originally Posted by shammat

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

r937 · #8 (**permalink**) 01-27-11, 09:31

2 digits = TINYINT

db_newbie · #9 (**permalink**) 01-27-11, 09:58

Quote:

Originally Posted by r937

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

r937 · #10 (**permalink**) 01-27-11, 10:02

Quote:

Originally Posted by db_newbie

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

it-iss.com · #11 (**permalink**) 01-27-11, 10:02

Try just:

Code:

CREATE TABLE test
(id TINYINT AUTO_INCREMENT,
...
);

shammat · #12 (**permalink**) 01-27-11, 10:09

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

db_newbie · #13 (**permalink**) 01-27-11, 10:19

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

db_newbie · #14 (**permalink**) 01-27-11, 10:45

Actually, NVM

It was my mistake; an extra bracket.

Thank you all

Internet Services

Web Development

Digital Marketing

Consumer Tech