SQL-约束

 

 

create table student(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    age int check ( age > 0 && age <= 120 ),
    status char(1) default 1,
    gender char(1)
);

 

 

create table kept(
    name varchar(10) unique not null,
    ky_id int primary key auto_increment
);
insert into kept (name) values ('科研部'),('外贸部'),('财务部');

alter table user add kept_id int;
update user set kept_id = 1 where name like '_';
update user set kept_id = 2 where name like '__';
update user set kept_id = 3 where name like '___';

alter table user add constraint tables_user_kept_id foreign key (kept_id) references kept(ky_id);
alter table user drop foreign key tables_user_kept_id;

 

alter table user add constraint tables_user_kept_id foreign key (kept_id) references kept(ky_id) on update cascade on delete cascade;
alter table user add constraint tables_user_kept_id foreign key (kept_id) references kept(ky_id) on update set null on delete set null;