CODEWITHSUNDEEP
mysqlsyntaxcreate-table
CoR
CoR

Reputation: 3914

Need clarification on mysql syntax

CREATE TABLE `table1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`)
)

-

CREATE TABLE `table2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`email` varchar(128) DEFAULT NULL UNIQUE,
)

Questions:

  1. When created, is there any difference between table1 and table2?
    (I assume no, but better to ask than live in blissful ignorance;)
  2. if (yes) what is it?; if (no) why do we have different syntax?
  3. in larger tables show create table tbl_name displays UNIQUE KEY email_2 (email) or some other _num. Why? What does tlb_name_num mean and what is it used for?
  4. What syntax is preferable and why?
  5. show create table tbnm always displays sql in table1 form, even if table is created by table2 sql syntax. Why?
  6. UNIQUE KEY (email) also works. but it's transformed to UNIQUE KEY email (email) on show create table. Why does it work, why is it transformed, etc?

Upvotes: 0

Views: 32

Answers (1)

mitchken
mitchken

Reputation: 800

  1. There will be no difference between the two tables.
  2. there are different syntaxes to declare everything first and then add primary key, unique ... Or if some people like to do it immeadiately they also have the possibility
  3. No idea, edit this if found
  4. they both do the same thing, use what you prefer.
  5. No idea, edit this if found
  6. No idea, edit this if found

Upvotes: 1

Related Questions