MySql 外键约束

 

# 创建外键约束

# 创建表b_book

create table b_book(

        id int(8) primary key auto_increment,

        name varchar(30),

        price float(3),

        date varchar(20)

)

# 创建表s_student

 create table s_student(

        id int(8) primary key auto_increment,

        name varchar(30),

        sex varchar(1),

        age int(2),

        cno int(8),

        constraint f_s_student_cno foreign key (cno) references b_book(id)

)

 #语法:constraint 外键约束名 foreign key (字段名) references 表（字段名）

#给已有表添加外键约束

# 创建表bb_book

create table bb_book(

        id int(8) primary key auto_increment,

        name varchar(30),

        price float(3),

        date varchar(20)

)

#创建表ss_student

create table ss_student(

        id int(8) primary key auto_increment,

        name varchar(30),

        sex varchar(1),

        age int(2),

        cno int(2)

)

-- 语法: alter table 表名 add constraint 外键约束名foreign key (字段名) references 表（字段名）

alter table ss_student add constraint f_ss_student_cno foreign key(cno) references bb_book(id)

# 删除外键约束

-- 语法:alter table 表名 drop foreign key 外键约束名

alter table ss_student drop foreign key f_ss_student_cno

# 有关联的表删除

-- 1.先删除有外键约束的表

drop table ss_student

-- 2.在删除主表

drop table bb_book