PRIMARY KEY

PRIMARY KEY

A primary key is really a type of UNIQUE constraint that identifies one or more columns that uniquely locate a single row within a given table. When you create a primary key, MySQL builds an index (PRIMARY), thus guaranteeing that no two rows will ever have the same values in that key. If you attempt to violate this constraint via an INSERT or UPDATE, the engine returns an error to your application.

Primary keys are essential for maintaining database integrity, and they also help speed things up for queries and other operations that need to find a row quickly.

You have at least three methods to define a primary key:

• Directly via SQL: If the primary key contains more than one column, you must specify this at the end of your CREATE/ALTER TABLE statement.

   CREATE TABLE checked_luggage (     luggage_check_id INT AUTO_INCREMENT PRIMARY KEY,     flight_id INT ... ... ); CREATE TABLE checked_luggage (     luggage_check_id INT AUTO_INCREMENT,     flight_id INT, ... ...     PRIMARY KEY (luggage_check_id, flight_id) ); 

  
  

• Implicitly, by creating a unique index:

   CREATE TABLE checked_luggage (     luggage_check_id INT UNIQUE,     flight_id INT ... ... ); 

  
  

• Via the MySQL Table Editor (see Figure 5.1):

  Figure 5.1. Using the MySQL Table Editor to define a primary key.

  
  
  

What is the difference between a primary key and a unique constraint? Both serve to identify one and only one row in a table. However, primary keys can consist of multiple columns that, when grouped together, form a unique value. On the other hand, a unique constraint can apply to only one column at a time.

You might encounter situations in which a table contains a system-generated value, which serves as a primary key, and another user-supplied value that is also unique. An example is a customer table in which the system generates primary key values using the AUTO_INCREMENT option in the CREATE TABLE statement while also storing a unique external value, such as a Social Security number or other government-generated value.