MySQL创建数据表并建立主外键关系详解

create table demo.ChineseCharInfo(ID int not null auto_increment,Hanzi varchar(10) not null,primary key (ID))engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;create table demo.ChinesePinyinInfo(ID int not null auto_increment,CharID int null,Pinyin varchar(10) null,Tone tinyint unsigned null,primary key (ID),-- 方式一：不指定外键名称，数据库自动生成foreign key (CharID) references ChineseCharInfo(ID) on delete cascade on update cascade -- 方式二：指定外键名称为(FK_Name)-- constraint FK_Name foreign key (CharID) references ChineseCharInfo(ID) on delete cascade on update cascade )engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

-- 为表(demo.ChinesePinyinInfo)中字段(CharID)添加外键，并指定外键名为(FK_Name)alter table demo.ChinesePinyinInfo add constraint FK_Name foreign key (CharID) references ChineseCharInfo(ID);-- 为表(demo.ChinesePinyinInfo)中字段(CharID)添加外键，不指定外键名，由数据库自动生成外键名alter table demo.ChinesePinyinInfo add foreign key (CharID) references ChineseCharInfo(ID);

-- 通过修改列的属性来删除自增长，第一个(ID)为原列名，第二个(ID)为新列名alter table demo.ChinesePinyinInfo change ID ID int not null;-- 删除表(demo.ChinesePinyinInfo)中的主键约束，如果主键列为自增列，则需要先删除该列的自增长alter table demo.ChinesePinyinInfo drop primary key;-- 删除表(demo.ChinesePinyinInfo)中的名称为(FK_Name)的外键alter table demo.ChinesePinyinInfo drop foreign key FK_Name;