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.
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.
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.
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.
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