content_image/709777.jpg
schema (database), per constraint type. A duplicate symbol  results in an error. See also the discussion about length limits of generated constraint identifiers at Section 9.2.1, “Identifier Length Limits” . 

<img src='content_image/709777.jpg'>

## Note 

If the CONSTRAINT symbol  clause is not given in a foreign key definition, or a symbol  is not included following the CONSTRAINT  keyword, MySQL uses the foreign key index name up to MySQL 8.0.15, and automatically generates a constraint name thereafter. 

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema. Consequently, names for each type of constraint must be unique per schema, but constraints of different types can have the same name. 

## • PRIMARY KEY 

A unique index where all key columns must be defined as NOT NULL . If they are not explicitly declared as NOT NULL , MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY . The name of a PRIMARY KEY  is always PRIMARY , which thus cannot be used as the name for any other kind of index. 

If you do not have a PRIMARY KEY  and an application asks for the PRIMARY KEY  in your tables, MySQL returns the first UNIQUE  index that has no NULL  columns as the PRIMARY KEY . 

In InnoDB  tables, keep the PRIMARY KEY  short to minimize storage overhead for secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row. (See Section 15.6.2.1, “Clustered and Secondary Indexes” .) 

In the created table, a PRIMARY KEY  is placed first, followed by all UNIQUE  indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE  keys. 

A PRIMARY KEY  can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY  key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY( key_part , ...)  clause. 

If a table has a PRIMARY KEY  or UNIQUE NOT NULL  index that consists of a single column that has an integer type, you can use _rowid  to refer to the indexed column in SELECT  statements, as described in Unique Indexes. 

In MySQL, the name of a PRIMARY KEY  is PRIMARY . For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix ( _2 , _3 , ... ) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name . See Section 13.7.7.22, “SHOW INDEX Statement” . 

## • KEY | INDEX 

KEY  is normally a synonym for INDEX . The key attribute PRIMARY KEY  can also be specified as just KEY  when given in a column definition. This was implemented for compatibility with other database systems. 

## • UNIQUE 

A UNIQUE  index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE 

