2.09 Primary Keys and Foreign Keys

rimary key

a column, or group of columns, used to identify a row.

is usually the table's first column and appears on the left of table diagrams, but the position is not significant to the database.

• Primary keys (simple or composite) must be:

  • UNIQUE. This rule ensures that each value identifies at most one row.

  • NOT NULL. This rule ensures that each value identifies at least one row.

simple primary key

key consists of a single column.

composite primary key

consists of multiple columns

auto-increment

is a numeric column that is assigned an automatically incrementing value when a new row is inserted.

AUTO_INCREMENT

keyword defines an auto-increment column. AUTO_INCREMENT follows the column's data type in a CREATE TABLE statement.

database users occasionally make the following errors when inserting primary keys:

• Inserting values for auto-increment primary keys.

• Omitting values for primary keys that are not auto-increment columns.

• MySQL allows insertion of a specific value to an auto-increment column. However, overriding auto-increment for a primary key is usually a mistake.

foreign key

is a column, or group of columns, that refer to a primary key.

FOREIGN KEY/REFERENCES

constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords.

When a foreign key constraint is specified, the database rejects insert, update, and delete statements that violate referential integrity.