Simple question i've always wondered

Databases
1

I wonder what is the difference between these three CREATE TABLE statements…

CREATE TABLE IF NOT EXISTS Files (
files_id smallint(10) unsigned NOT NULL AUTO_INCREMENT,
filename varchar(250) DEFAULT NULL,
original_filename varchar(250) DEFAULT NULL,
date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (files_id)
)
and

CREATE TABLE IF NOT EXISTS Files (
files_id smallint(10) unsigned NOT NULL AUTO_INCREMENT,
filename varchar(250) DEFAULT NULL,
original_filename varchar(250) DEFAULT NULL,
date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (files_id`)
)
and

CREATE TABLE IF NOT EXISTS ‘Files’ (
‘files_id’ smallint(10) unsigned NOT NULL AUTO_INCREMENT,
‘filename’ varchar(250) DEFAULT NULL,
‘original_filename’ varchar(250) DEFAULT NULL,
‘date_added’ timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (‘files_id’)
)
Whats the difference between using `, ', nothing in the table name and the column names. Also do CAPS matter at all. and lastly does it matter if you declare the PRIMARY KEY in the column row or at the end in the CREATE TABLE statement? and also when declaring a FOREIGN KEY, whats the difference between
column_name REFERENCES Table(id),
and
FOREIGN KEY (column_name),
Thanks

2

the first two CREATE TABLE statements are equivalent (except you have a dangling backtick in the PK of the second one)

the third one is garbage and won’t run (you didn’t test it, did you)

regarding caps, please read this: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

PRIMARY KEY can be specified either on the column definition if only one column is the primary key, or as a separate clause

can the REFERENCES attribute be declared on the column? this one i think you can test yourself, yes?

:slight_smile:

3

ok, read up on the case thing…
Why then (is there any advantage to) is the ` use sometimes, is it asically useless?

4

the advantage is that backticks allow you to create a table or column name which contains a special character (including a space or dash) or which is a reserved word

not that you should do that, but you can

in all other cases backticks are less than useless (since useless suggests no use, a zero value) – they are actually counterproductive, adding significant noise to the query without any increase in signal

in standard sql, you use double quotes for that purpose – “total sales”

in microsft access and sql server, square brackets – [total sales]

5

ok, thanks, i’ve always was confused with that…