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);