PRIMARY KEY definition in MySQL CREATE TABLE statement

Question

What's the difference between this code:

CREATE TABLE samples (
  sampleid INT(11) NOT NULL AUTO_INCREMENT,
  sampledate DATE NOT NULL,
  location VARCHAR(25) NOT NULL,
  PRIMARY KEY (sampleid)
)
ENGINE=InnoDB;

and this:

CREATE TABLE samples (
  sampleid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sampledate DATE NOT NULL,
  location VARCHAR(25) NOT NULL,
)
ENGINE=InnoDB;

code?

So a separate PRIMARY KEY statement or as part of a column definition. Same question for UNIQUE INDEX and UNIQUE keyword in column definition.

Answer 1

The second syntax is merely a shortcut allowing you to specify the column and add an index on it in a single clause.

This works out fine in cases where you simply want to create a column and add an index on it.

You'll need to use the first syntax if you want to do something more complicated, such as adding an index based on multiple columns rather than a single column, or if you are adding or changing an index on an existing column; that is, you are not creating the column and the index on it at the same time.

Answer 2

MySQL allows uses the PRIMARY KEY directive to allow you to set the Primary Key dynamically. Supplying PRIMARY KEY as an argument to the constructor can only be called on creating the column. PRIMARY KEY(X), PRIMARY KEY(Y), PRIMARY KEY(Z) allows for changing the primary keys on subsequent queries.

Answer 3

The way I see it is.. The first method is used to create composite keys. While the second method (more readable to me) is primarily used if there is only primary key in the table.

The second method cannot be used if you want to implement composite key