MySQL之完整性约束

database

完整性约束

完整性约束的定义

为了保证插入数据的正确性和合法性,给表中字段添加,除了数据类型约束以外的其他约束条件

完整性约束的分类

  • 实体完整性:记录之间不能重复。

    • 主键约束(primary key):唯一并且不能为空
    • 唯一约束(unique):唯一可以为空
    • 主键自增(auto_increment)

  • 域完整性:数据库表的字段,必须符合某种特定的数据类型或约束。

    • 类型约束:在创建表的时候,已经给每个字段添加类型了
    • 非空约束:not null
    • 默认值:default

  • 引用完整性(参照完整性):一张表中字段的值,需要参考另外一张表中的值。

    • 添加外键约束:foreign key
    • 引用完整性会降低sql的执行效率,有时候能不用就不用。

主键约束(primary key)

  • 主键约束(primary key):唯一并且不能为空;
  • 一张表只能有一个主键字段,但是可以有联合主键;

添加主键约束,有以下3种方式:

方式一:在创建表的同时,添加主键约束。

create table student(

sid int primary key,

sname varchar(20),

age int

)charset=utf8;

-- 添加主键约束后,插入相同的sid,会报错。

insert into student(sid,sname,age) values (1,"张三",22);

insert into student(sid,sname,age) values (1,"李四",33);

方式二:在创建表的同时,添加主键约束,但是添加方式不同。

"只有这种添加主键的方式,可以添加联合主键"

create table student(

sid int,

sname varchar(20),

age int,

primary key(sid)

)charset=utf8;

方式三:创建完表后,添加主键约束。

create table student(

sid int,

sname varchar(20),

age int

)charset=utf8;

alter table student add primary key(sid);

-- constraint表示添加的是一个约束;

-- pk_sid是给约束起一个名字;

单个主键和联合主键的区别

"单个主键"

将表中某一个字段设置为主键,用这一个字段,来唯一确定一条记录。

只要sid唯一不为空,每一行就是一条唯一的记录。

primary key(sid)

sid sname age

1 张三 18

2 张三 18

"联合主键"

将表中2个字段(或多个字段)设置为联合主键,用这2个字段(或多个字段),

来唯一确定一条记录。

这2个字段可以分别重复,只要不是同时重复即可。

primary key(sid,classid)

sid classid sname age

1 2 张三 18

1 3 张三 18

2 4 李四 20

3 4 王五 30

-- 下面两条记录,联合字段同时重复,表示一条重复记录,插入时报错。

5 5 李闯 25

5 5 王二 24

主键字段的挑选原则

  1. 通常选择无意义的字段作为主键字段,比如说表中记录每一行行号的id字段,就是无意义的字段,很适合作为主键

  2. 主键字段一般不会对其进行修改(像字段名、字段类型等)

  3. 经常变化的字段,有意义的字段,不适合作为主键

特别注意:当一个建表语句中,某个字段只有primary key限制,而没有使用主键自增auto_increament

需要我们自己给主键字段插入值,否则会出现如下错误。"ERROR 1364 (HY000): Field "sid" doesn"t have a default value"

但是我们上面说过,主键字段一般是不进行修改的,也就是说不要主动给他值,主键字段应该是让系

统默认赋值。这个在学习过主键自增后你就明白了。

记住:primary keyauto_increament最好配合使用。

举例说明:

删除主键约束

alter table student drop primary key;

主键自增(auto_increment)

主键自增的含义和特点

见名知意,这个是用来帮助主键自动添加值的一个约束。上面我们说过,主键字段最好不要有任何改动,当然也包括给主键字段插入数值的时候,也不要手动去添加主键,而是让系统自动给主键字段进行赋值。主键自增有如下2个特点:

  1. 设置了主键自增后,主键值会从1开始,每次自增1。
  2. 以前用过的主键值,当你删除该行记录后,主键值仍然是一个新的值,并不会重复使用以前出现过的主键值。

对第二个特点的说明:

添加唯一约束,有以下2种方式:

方式一:创建表的时候,在添加主键的同时,添加主键自增。 

create table student(

sid int primary key auto_increment,

sname varchar(20),

age int,

idcard varchar(18)

)charset=utf8;

-- primary key和auto_increment配合使用,插入数据的时候,

-- 不需要给主键字段sid赋值了。

insert into student(sname,age) values ("张三",22),("李四",25);

方式二:创建完表后,给字段同时添加"主键和主键自增"。

create table student(

sid int,

sname varchar(20),

age int,

idcard varchar(18)

)charset=utf8;

-- 以下2种方式,均可以给字段同时添加"主键和主键自增"。

alter table student modify sid int primary key auto_increment;

alter table student change sid sid int primary key auto_increment;

删除主键自增

alter table student modify sid int primary key;

唯一约束(unique)

唯一性约束的含义

由于一张表中只能由一个主键。但是对于某些字段,像身份证这样的字段,肯定也是唯一的,我们不能又将其设置为主键(身份证字段属于有意义的字段),那么怎么保证它的唯一性呢?这就是接下来要讲述的唯一约束。注意:一张表可以有多个唯一约束。

添加唯一约束,有以下3种方式:

方式一:在创建表的同时,添加唯一约束。

create table student(

sid int primary key key auto_increment,

sname varchar(20),

age int,

idcard varchar(18) unique

)charset=utf8;

方式二:在创建表的同时,添加唯一约束,但是添加方式不同。

"只有这种添加主键的方式,可以添加联合主键"

create table student (

sid int primary key auto_increment,

sname varchar(20),

age int,

idcard varchar(18),

unique(idcard)

)charset=utf8;

方式三:创建完表后,添加唯一约束。

create table student (

sid int primary key auto_increment,

sname varchar(20),

age int,

idcard varchar(18)

)charset=utf8;

alter table student add unique(idcard);

-- 设置了唯一约束的字段,当插入相同的值,会报错。

insert into student(sname,age,idcard) values ("李四",18,"123456");

insert into student(sname,age,idcard) values ("王五",22,"123456");

删除唯一约束

删除唯一约束,使用的语句有点不同。

alter table student drop key idcard;

添加非空约束(not null)

添加非空约束

非空约束指的是我们给某个字段设置了非空约束后,当我们给该字段插入空值的时候,会报错。添加非空约束,有以下2种方式:

方式一:创建表的时候,给字段添加非空约束。 

create table student(

sid int primary key auto_increment,

sname varchar(20) not null,

age int,

idcard varchar(18)

)charset=utf8;

方式二:创建完表后,给字段添加非空约束。

create table student(

sid int primary key auto_increment,

sname varchar(20),

age int,

idcard varchar(18)

)charset=utf8;

-- 有以下两种添加方式。

alter table student modify sname varchar(20) not null;

alter table student change sname sname varchar(20) not null;

字段添加非空约束后具有如下特点

  1. 字段设置了非空约束后,你不能插入null值。
  2. 字段设置了非空约束后,不但不能插入null值,而且还不能不插入值

"这些细节知识,有时候是我们没有注意到的,因此单独拿出来说明。"

一:字段设置了非空约束后,你不能插入null值。

-- 下面这样插入会报错。

insert into student(sname,age) values (null,22);

二:字段设置了非空约束后,不但不能插入null值,而且还不能不插入值。

-- 下面这样插入也会报错。

insert into student(age,idcard) values (33,"123456789");

举例如下:

添加默认值约束(default)

某个字段当被设置了默认值以后,当我们不为该字段指定值的时候,就会显示这个默认值。

添加默认值约束

方式一:创建表的时候,给字段添加默认值。 

create table student(

sid int primary key auto_increment,

sname varchar(20),

age int default 0,

idcard varchar(18)

)charset=utf8;

方式二:创建完表后,给字段添加默认值。

create table student(

sid int primary key auto_increment,

sname varchar(20),

age int,

idcard varchar(18)

)charset=utf8;

"不同点,需要特别留意"

-- 使用alter添加默认值的代码有点特别。

alter table student alter age set default 0;

删除默认值

alter table student alter column age drop default;

引用完整性(参照完整性)

什么是引用完整性?

引用完整性指的是,一张表中字段的取值,需要参照另一张表中某个字段的取值。这里涉及到的就是外键:foreign key。一般情况下,本表中的这个字段设置的是外键foreign key,参考表中的那个字段的取值,就需要设置为那个表的主键。

参考下图帮助理解:

特别注意:学生表中的cid参考的是班级表中的cid,可以看到,这里的字段名是完全一样的。但是,实际上,这两个字段名可以不一样,但是这两个字段的数据类型必须是一样的

构成引用完整性的条件:

  1. 一张表(参照表)中的某个字段,参照另一张表(被参照表)中的字段(字段名可以不同)。
  2. 两张表中的字段名可以不同,但是字段的数据类型必须一致
  3. 参照表中的字段在该表中是foreign key。被参照表中的字段在该表中是primary key

添加外键约束(foreign key)

需要记住一句话:先有被引用的表,然后才可以给当前表添加外键约束,否则,你引用谁去?

添加外键约束,有以下2种方式:

方式一:创建表的时候,给字段添加外键约束。 

create table classroom(

cid int primary key auto_increment,

cname varchar(20) not null

)charset=utf8;

create table student(

sid int primary key auto_increment,

sname varchar(20) not null,

age bit(1),

cid int,

constraint fk_cid foreign key(cid) references classroom(cid)

)charset=utf8;

方式二:创建完表后,给字段添加外键约束。

create table classroom(

cid int primary key,

cname varchar(20) not null

)charset=utf8;

create table student(

sid int primary key auto_increment,

sname varchar(20) not null,

age bit(1),

cid int

)charset=utf8;

alter table student add constraint fk_cid foreign key(cid)

references classroom(cid);

"注意:constraint后面接的是别名,fk_cid相当于起的别名。"

以上是 MySQL之完整性约束 的全部内容, 来源链接: utcz.com/z/535646.html

回到顶部