mysql添加外键约束

-- 先创建表后关联外键
-- ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
-- FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

 

-- 关联外键 创建表时关联外键

create table info(
id int not null auto_increment primary key,
name varchar(16) not null,
email varchar(32) not null,
age int,
depart_id int not null,
constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;

 

--簡單版

create table user(
id int(11) not null auto_increment PRIMARY KEY,
username VARCHAR(80) not NULL,
password VARCHAR(120) not NULL
)

CREATE table article(
id int(11) not null auto_increment PRIMARY KEY,
title VARCHAR(200) not null,
content text not null,
u_id int(11) not null,
foreign key (u_id) references user(id)
)

 

 

--多对多 创建中间表 中间表关联对于的外键

CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`sno` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`)
)DEFAULT CHARSET=utf8;

 

CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
)DEFAULT CHARSET=utf8;

CREATE TABLE `stu_course`(
`s_id` int(11) NOT NULL,
`c_id` int(11) NOT NULL,
CONSTRAINT fk_s_id FOREIGN key (s_id) references stu(id),
CONSTRAINT fk_c_id FOREIGN key (c_id) references course(id)
)

 

 

 

CREATE TABLE `categorys` (
`id` int(10) NOT NULL COMMENT '主键id',
`category_id` int(10) NOT NULL unique,
`category_page` int(10) NOT NULL COMMENT '目录页数',
`category_title` varchar(255) DEFAULT NULL COMMENT '标题',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `category_id` (`category_id`),
UNIQUE KEY `category_id_2` (`category_id`),
UNIQUE KEY `category_id_3` (`category_id`)
)

#设计外键表的时候 需要主表的主键当外键，或者非主键 但是该字段一定是要not null unique 并且字符类型一致 字符位数也要一致 同样是要是int(10)
CREATE TABLE `videos` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`category_id` int(10) not NULL,
`m3u8` text,
`v_cover` text,
`v_length` varchar(20) DEFAULT NULL,
`v_title` varchar(100) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `fk_category_id` (`category_id`),
CONSTRAINT `fk_category_id` FOREIGN KEY (`category_id`) REFERENCES `categorys` (`category_id`)
)

ALTER TABLE categorys MODIFY category_id int(10) NOT NULL UNIQUE;