约束分为表级约束和列级约束,列级约束包括,主键,唯一,非空,默认值;外键约束一般写在表级约束里面

create table if not exists major(
    id int primary key,
    name varchar(20)
)

create table if not exists info(
    id int primary key,
    name varchar(20) not null,
     gender char(1),
    age int default 18,
    seat int unique,
    majorid int,
    constraint fk_info_major foreign key(majorid) referencer major(id) 
);

desc info;
show index from info;

1.注意外键字段必须是关联的主键或唯一键,关联其他键会报错

2.注意创建顺序,一定是先创建表,再创建带约束的关联表

3.插入和删除关联记录时候,注意顺序;插入先major后info;删除先info,后major

主键和唯一的区别

主键特点唯一,非空,一张表中至多一个主键,允许组合
唯一:可以为空,一张表中可以有多个唯一键,允许组合

create table if not exists info(
    id int,
    name varchar(20) not null,
     gender char(1),
    age int default 18,
    seat int,
    majorid int,
    constraint fk_info_major foreign key(majorid) referencer major(id),
    primary key(id,name),
    unique(age,seat)
);

自增id

alter table info modify COLUMN id int auto_increment

级联删除

constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete cascade;

alter table info add constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete cascade ;

级联置空

constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete set null;

alter table info add constraint fk_info_major foreign key(majorid) REFERENCES major(id) on delete set null;