MySQL Table Creation Table creation with Primary Key
Example
CREATE TABLE Person (
PersonID INT UNSIGNED NOT NULL,
LastName VARCHAR(66) NOT NULL,
FirstName VARCHAR(66),
Address VARCHAR(255),
City VARCHAR(66),
PRIMARY KEY (PersonID)
);
A primary key is a NOT NULL single or a multi-column identifier which uniquely identifies a row of a table. An index is created, and if not explicitly declared as NOT NULL, MySQL will declare them so silently and implicitly.
A table can have only one PRIMARY KEY, and each table is recommended to have one. InnoDB will automatically create one in its absence, (as seen in MySQL documentation) though this is less desirable.
Often, an AUTO_INCREMENT INT also known as "surrogate key", is used for thin index optimization and relations with other tables. This value will (normally) increase by 1 whenever a new record is added, starting from a default value of 1.
However, despite its name, it is not its purpose to guarantee that values are incremental, merely that they are sequential and unique.
An auto-increment INT value will not reset to its default start value if all rows in the table are deleted, unless the table is truncated using TRUNCATE TABLE statement.
Defining one column as Primary Key (inline definition)
If the primary key consists of a single column, the PRIMARY KEY clause can be placed inline with the column definition:
CREATE TABLE Person (
PersonID INT UNSIGNED NOT NULL PRIMARY KEY,
LastName VARCHAR(66) NOT NULL,
FirstName VARCHAR(66),
Address VARCHAR(255),
City VARCHAR(66)
);
This form of the command is shorter and easier to read.
Defining a multiple-column Primary Key
It is also possible to define a primary key comprising more than one column. This might be done e.g. on the child table of a foreign-key relationship. A multi-column primary key is defined by listing the participating columns in a separate PRIMARY KEY clause. Inline syntax is not permitted here, as only one column may be declared PRIMARY KEY inline. For example:
CREATE TABLE invoice_line_items (
LineNum SMALLINT UNSIGNED NOT NULL,
InvoiceNum INT UNSIGNED NOT NULL,
-- Other columns go here
PRIMARY KEY (InvoiceNum, LineNum),
FOREIGN KEY (InvoiceNum) REFERENCES -- references to an attribute of a table
);
Note that the columns of the primary key should be specified in logical sort order, which may be different from the order in which the columns were defined, as in the example above.
Larger indexes require more disk space, memory, and I/O. Therefore keys should be as small as possible (especially regarding composed keys). In InnoDB, every 'secondary index' includes a copy of the columns of the PRIMARY KEY.
