SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,618
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    simple question i've always wondered

    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
    "Oh, and Jenkins--apparently your mother died this morning."

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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/...nsitivity.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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,618
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ok, read up on the case thing...
    Why then (is there any advantage to) is the ` use sometimes, is it asically useless?
    "Oh, and Jenkins--apparently your mother died this morning."

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,618
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ok, thanks, i've always was confused with that...
    "Oh, and Jenkins--apparently your mother died this morning."


Bookmarks

Posting Permissions

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