Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PRIMARY KEY definition in MySQL CREATE TABLE statement

Tags:

mysql

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.

like image 883
waanders Avatar asked Sep 08 '11 15:09

waanders


People also ask

What is key in MySQL CREATE TABLE?

In MySQL, a key is a data item that identifies a record exclusively. In other terms, the key is a group of columns used to uniquely define a record in a table. It is used to retrieve or extract rows from a table as needed.

How can we CREATE TABLE with primary key in SQL?

Now, to create a PRIMARY KEY constraint on any column when the table already exists (NO EARLIER PRIMARY KEY DEFINED), use the following SQL Syntax: ALTER TABLE [Table_Name] ADD PRIMARY KEY (ID); Query: ALTER TABLE Employee ADD PRIMARY KEY (Phone_No);

What clause of the CREATE TABLE statement do you use to create a primary key?

Include PRIMARY KEY in the ADD clause with the ALTER TABLE statement to add a primary key to a table definition. Before adding the primary key, you must ensure that the columns in the primary key column list are defined as NOT NULL. A primary key is a unique index and can be created only on not nullable columns.

How can we define a primary key in a table?

A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table must have a primary key for Optim to insert, update, restore, or delete data from a database table. Optim uses primary keys that are defined to the database.


3 Answers

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.

like image 180
thomasrutter Avatar answered Oct 08 '22 22:10

thomasrutter


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.

like image 26
htmldrum Avatar answered Oct 08 '22 22:10

htmldrum


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

like image 45
user4826347 Avatar answered Oct 09 '22 00:10

user4826347