MySQL primary and foreign key creation:
(1) Ensure that the referenced tables and fields exist.
(2) The associated table must be of the InnoDB Storage type.
(3) The primary key of the primary join table must be set.
(4) The primary key and foreign key data types and character encoding (unsigned) must be consistent
(5) Ensure that the syntaxes of the preceding statements are correct.
Appendix: MySQL creates a table. The default type is MyISAM.
To change the default table type, add default_storage_engine = InnoDB in my. INF.
SQL statement example for creating a foreign key table:
Master table:
Create Table 'Building _ info '(
'Buildingid' int (4) unsigned not null auto_increment,
'Buildingname' varchar (50) not null default '',
'Buildingdesc' varchar (100) default '',
Primary Key ('buildingid ')
) Engine = InnoDB default charset = gb2312;
Foreign key table (associated field: buildingid ):
Create Table 'floor _ info '(
'Floorid' int (4) unsigned not null auto_increment,
'Buildingid' int (4) unsigned not null default '0 ',
'Floorname' varchar (50) not null default '',
'Floorindex' int (4) not null default '0 ',
Primary Key ('floorid '),
-- Key 'floor _ info_fk_buildingid' ('buildingid '),
Constraint 'floor _ info_fk_buildingid' foreign key (buildingid) References building_info (buildingid)
) Engine = InnoDB default charset = gb2312;
Appendix: You can create a table and then create an index:
Example:
Alter table floor_info add constraint 'floor _ info_fk _ buildingid 'foreign key (floorid) References building_info (floorid );