mysql命令行外键设置_Mysql常用命令行大全（四）外键及其它

表构成

mysql> show tables;

+----------------------+

| Tables_in_WebComplie |

+----------------------+

| Compile |

| Content |

| User      |

+----------------------+

Compile表结构

mysql> show create table Compile;

| Compile | CREATE TABLE `Compile` (

`compileID` int(11) NOT NULL AUTO_INCREMENT,

`userName` varchar(128) NOT NULL,

`boardName` varchar(128) NOT NULL,

`panelName` varchar(128) NOT NULL,

`contentID` int(11) NOT NULL,

`complieDate` datetime NOT NULL,

`binPath` varchar(128) NOT NULL,

`compileState` tinyint(1) NOT NULL,

PRIMARY KEY (`compileID`),

UNIQUE KEY `UQ_Compile_CompileID` (`compileID`),

KEY `ContentID` (`contentID`),

KEY `Compile_Panel_PanelName` (`panelName`),

KEY `Compile_User_UserName` (`userName`),

CONSTRAINT `Compile_User_UserName` FOREIGN KEY (`userName`) REFERENCES `User` (`userName`),

CONSTRAINT `Compile_ibfk_2` FOREIGN KEY (`contentID`) REFERENCES `Content` (`contentID`),

CONSTRAINT `Compile_Panel_PanelName` FOREIGN KEY (`panelName`) REFERENCES `Panel` (`PanelName`)

) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 |

删除外键“UserID”：

mysql> alter table Compile drop foreign key Compile_User_UserName;  //注意删除的是外键约束“Compile_User_UserName”，即外键UserID

增加外键：

mysql> alter table Compile add constraint Compile_User_UserName foreign key(userName) references User(userName);