mysql创建主外键关联_mysql主外键建立心得

mysql主主外键建立：

(1)、确保参照的表和字段是存在的

(2)、关联表必须是Innodb存储类型

(3)、必须设置主关联表主键

(4)、主键与外键数据类型和字符编码(unsigned)必须一致

(5)、确保以上声明的句法是正确的

附：mysql建立表默认类型为：MYISAM

如果要改变默认表类型可在my.inf中加：default_storage_engine=INNODB

创建加外键表SQL语句示例：

主表：

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;

外键表(关联字段：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;

附：还可以建立好了表再创建索引：

例：alter table FLOOR_INFO add constraint `Floor_Info_FK_ BuildingID` foreign key (FloorID) references  BUILDING_INFO(FloorID);