六种约束创建
1. NOT NULL约束
字段设置为not null
create table StudentInfo(
id int not null,
name varchar(8) not null,
gender varchar(2) not null,
)
2. default约束
再插入信息时可不给定值
-
创建表时给定默认值
create table StudentInfo( id int not null, name varchar(8) not null, gender varchar(2) not null default('男'), classid varchar(4) not null )
-
修改表:给表的某一字段设定默认值
alter table StudentInfo add constraint df_classid default('男') for gender
3. unique约束
-
创建表时设置唯一性约束
create table StudentInfo( id int not null, name varchar(8) unique not null, gender varchar(2) not null default('男'), classid varchar(4) not null )
-
修改表时为字段设置唯一性约束
alter table StudentInfo add constraint unique_name unique(name)
4. check约束
-
创建表时给定check约束
注:创建多个check约束时必须要写到最后
create table StudentInfo( id int not null, name varchar(8) not null unique, gender varchar(2) not null default('男'), classid varchar(4) not null, score int, check(score >= 0 and score <= 100) )
-
修改表时为字段设置check约束
alter table StudentInfo add constraint ck_score check(score between 0 and 100)
5. primary key约束
-
单一主键
-
创建表时为字段设置主键约束
create table StudentInfo( id int primary key not null, name varchar(8) not null unique, gender varchar(2) not null default('男'), classid varchar(4) not null, score int, check(score >= 0 and score <= 100) )
-
修改表时为字段设置主键约束
alter table StudentInfo add constraint pk_id primary key(id)
-
-
多个主键
- 创建时
create table StudentInfo( id int not null, name varchar(8) not null unique, gender varchar(2) not null default('男'), classid varchar(4) not null, score int, constraint pk_compound primary key(id, classid) )
-
修改时
alter table StudentInfo add constraint pk_compound primary key(id, classid)
6. foreign key约束
-
创建时
create table ClassInfo( classid varchar(4) primary key not null, id int not null foreign key references StudentInfo(id), score int, )
-
修改时
alter table ClassInfo add constraint fk_id foreign key(id) references StudentInfo(id)
7. 删除约束
alter table ClassInfo
drop constraint constraint_name