《Mysql基础》【Mysql添加外键（新增外键）、mysql添加主键、mysql删除外键】 编程入门 学习分享 【公开免费】

-- mysql数据库程序设计笔记：

-- 新建表：foreign key 加外键 举例：

create database db_test_1
default character set gb2312
default collate gb2312_chinese_ci;

use db_test_1;

create table a(
id int not null auto_increment comment 'id自增',
ainfo varchar(255),
primary key (id)
)engine=InnoDB default charset='utf8' comment 'a表备注 xxx表';

create table b(
id int not null auto_increment comment 'id自增',
binfo varchar(255),
primary key (id)
)engine=InnoDB default charset='utf8' comment 'b表备注 xxx表';

create table c(
a_cxxx_info int,
b_cxxx_info int,
cxxx_xxx varchar(255),
cxxx_ttt varchar(255),
constraint pk_c primary key (a_cxxx_info,b_cxxx_info),
constraint fk_c1 foreign key(a_cxxx_info) references a(id),
constraint fk_c2 foreign key(b_cxxx_info) references b(id)
)engine=InnoDB default charset='utf8' comment 'c 表名备注 xxx表';

-- 删除外键约束
mysql> alter table c drop foreign key fk_c1;
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table c\G;
*************************** 1. row ***************************
Table: c
Create Table: CREATE TABLE `c` (
`a_cxxx_info` int(11) NOT NULL DEFAULT '0',
`b_cxxx_info` int(11) NOT NULL DEFAULT '0',
`cxxx_xxx` varchar(255) DEFAULT NULL,
`cxxx_ttt` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a_cxxx_info`,`b_cxxx_info`),
KEY `fk_c2` (`b_cxxx_info`),
CONSTRAINT `fk_c2` FOREIGN KEY (`b_cxxx_info`) REFERENCES `b` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='c 表名备注 xxx表'
1 row in set (0.00 sec)

-- 添加外键约束：
mysql> alter table c add foreign key (a_cxxx_info) references a(id);
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table c\G;
*************************** 1. row ***************************
Table: c
Create Table: CREATE TABLE `c` (
`a_cxxx_info` int(11) NOT NULL DEFAULT '0',
`b_cxxx_info` int(11) NOT NULL DEFAULT '0',
`cxxx_xxx` varchar(255) DEFAULT NULL,
`cxxx_ttt` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a_cxxx_info`,`b_cxxx_info`),
KEY `fk_c2` (`b_cxxx_info`),
CONSTRAINT `c_ibfk_1` FOREIGN KEY (`a_cxxx_info`) REFERENCES `a` (`id`),
CONSTRAINT `fk_c2` FOREIGN KEY (`b_cxxx_info`) REFERENCES `b` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='c 表名备注 xxx表'
1 row in set (0.00 sec)