MySQL foreign key syntax

MySQL can use create table or alter table to define the MySQL foreign key. The following describes the syntaxes related to these two methods, and hopes to help you learn about MySQL Foreign keys.

Create table defines MySQL foreign key Syntax:

 
 

  
  
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name  
  
  
      [(create_definition,…)]  
  
  
 
  
  
create_definition:  
  
  
      column_definition  
  
  
    | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) [reference_definition]  
  
  
 
  
  
column_definition:  
  
  
      col_name type [NOT NULL | NULL] [DEFAULT default_value]  
  
  
          [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]  
  
  
          [COMMENT ’string’] [reference_definition]  
  
  
 
  
  
index_col_name:  
  
  
      col_name [(length)] [ASC | DESC]  
  
  
 
  
  
reference_definition:  
  
  
      REFERENCES tbl_name [(index_col_name,…)]  
  
  
                 [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]  
  
  
                 [ON DELETE reference_option]  
  
  
                 [ON UPDATE reference_option]  
  
  
 
  
  
reference_option:  
  
  
      RESTRICT | CASCADE | SET NULL | NO ACTION  
 
 

Alter table defines MySQL foreign key Syntax:

 
 

  
  
ALTER [IGNORE] TABLE tbl_name  
  
  
      alter_specification [, alter_specification] …  
  
  
 
  
  
alter_specification:  
  
  
    | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)  
  
  
    | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,…)  
  
  
    | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) [reference_definition]  
  
  
    | DROP FOREIGN KEY fk_symbol  
 
 

Four conditions for using the MySQL foreign key

Connection between MySQL Foreign keys and reference integrity

How to Set auto-increment fields in MySQL

Mysql inserts an instance of the Clob Field

Default length of common MySql Fields