比尔云BierYun--阿里云最新优惠活动
阿里云优惠码丨阿里云代金券

MySQL 数据约束

MySQL 数据约束http://www.bieryun.com/3584.html

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为,行为会被约束终止。约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。可以使用约束来限定表中数据准确性、完整性、一致性、联动性等。
sql有6中约束,not null, default , prmary key, foreign key ,unique ,check

create table student(
    id int auto_increment,
    name varchar(50) not null,
    age tinyint not null,
    sex varchar(5) default '男',
    address varchar(255),
    phone_num varchar(11) unique not null,
   primary key (id)
);
1.非空

没有给表字段添加默认值约束时,当我们插入数据的时候如果没有传递对应字段的数据则默认为NULL 即default null。如果需要限制某个字段必须赋值,可以添加 not null 约束

insert into student (name,age) values ('aa',null);
 --  Column 'age' cannot be null

insert into student (name,age) values ('aa',23);
-- Field 'phone_num' doesn't have a default value

字段添加非空约束以后,则必须传递有意义的数据,就是说不传值或者传递null值,都会报错‘字段不能为null’

2.默认值

我们在创建表或者修改表的时候,给某个字段添加默认值,这样如果为字段传入数据则为传递数据值,不提供数据就是用默认值。如果没有使用默认值约束,则默认使用default null,在上面也提到过了。

 show create table student;

 Table   | Create Table   
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) NOT NULL,
  `sex` varchar(5) DEFAULT '男',
  `address` varchar(255) DEFAULT NULL,
   `phone_num` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
  UNIQUE KEY `phone_num` (`phone_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  |

从上面也可以看到MySQL 自动为address使用了 default null

 insert into student (name,age,phone_num) values ('aa',23,'1766666612');
 select * from student;
+----+------+-----+------+---------+------------+
| id | name | age | sex  | address | phone_num  |
+----+------+-----+------+---------+------------+
|  1 | aa   |  23 | 男   | NULL    | 1766666612 |
3.唯一

当某个字段不允许重复时,可以使用 unique添加唯一约束。当添加数据的时候就会检查表中是否已经存在该数据,存在则报错否则正常插入。
注:unique 约束的字段可以为null,并且对null值不起限定作用(可以插入多个null)。所以unique 只对插入的有意义的数值起到限定

 UNIQUE KEY `phone_num` (`phone_num`)
 -- 第一个phone_num是unique约束名称(应该是索引名称),第二个在括号里的phone_num是字段的名称

MySQL删除unique 约束

alter table student drop {index|key} phone_num //删除唯一约束

从上面也可以看到,删除唯一约束时使用index或key(在MySQL中key和index意思差不多吧),大概是mysql唯一约束是利用唯一索引实现的吧。
从下面也可以看到,给列添加主键约束时,也会创建索引

添加唯一约束

alter table student add constraint stu_phone_num_con unique key  phone_num_unique (phone_num)
--或者,此时mysql会根据列名自动生成 约束的名称
alter table student add unique (phone_num)

从上面添加唯一约束的方式来看,也确实时创建了一个唯一索引。
可以为多列指定唯一约束,也可以为某列创建多个唯一约束。

4.主键

主键用于标识表的某一行数据,所以主键列数据不能重复 ,并且主键需要有实际意义。所以主键就有两种约束的特性,主键= not null + unique

--显示表信息 describe  tableName
 desc student;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| name      | varchar(50)  | NO   |     | NULL    |                |
| age       | tinyint(4)   | NO   |     | NULL    |                |
| sex       | varchar(5)   | YES  |     | 男      |                |
| address   | varchar(255) | YES  |     | NULL    |                |
| phone_num | varchar(11)  | NO   | UNI | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

1)可以看到id ,非空且唯一,而且 key列显示pri。在extraauto_incrementauto_increment 必须作用于主键上,而且这是MySQL独有的,在其他sql语言中一般称之为序列。
2) 很多字段默认值都为null(default null),但是使用not null约束以后default null实际上不起作用了

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。但是每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。一般主键使用自增的方式,在MySQL中使用auto_increment

索引除了主键索引(pri)和唯一索引(uni)还有普通索引(MUL)

5.外键

外键约束是保证两个表之间的参照完整性,一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
外键所在表称之为从表,主键所在表称之为主表。这样主表的数据不能随意修改或者直接更新从表数据。

1).使用外键约束
create table dep(
    id int primary key,
    name varchar(80) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create table teacher(
    id int primary key ,
    depId int ,
    name varchar(50) not null,
    foreign key (depId) references dep(id) 
)ENGINE=InnoDB CHARSET=utf8

teacher depId外键引用了dep 表中的id
需要说明的是,外键也可以为空。

2)新增操作
insert into teacher(id,name) values(1,'aa');
-- 可以插入成功

insert into teacher(id,name,depId) values(1,'aa',1);
-- 插入有意义的数据时就会去dep 表中查询是否存在该值,不存在则报错
-- Cannot add or update a child row: a foreign key constraint fails (`mydata`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `dep` (`id`))


 insert into dep(id,name) values(1,'计算机');
 insert into teacher(id,name,depId) values(2,'pig 奇',1);
3)更新操作
update dep set id =2 where id=1;

--  Cannot delete or update a parent row: a foreign key constraint fails (`mydata`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `dep` (`id`))

更新主表的被关联列时,该行为被阻止。可以使用级联操作强制数据更新

使用级联操作的两种方式
1.创建表的时候增加级联更新的选项

foreign key (depId) references dep(id)  on update cascade

2.修改表

-- 1.删除外键
alter table teacher drop foreign key teacher_ibfk_1;
-- 新增外键,添加级联更新选项
alter table teacher add foreign key(depId) references dep(id) on update cascade;

update dep set id =2 where id=1 ;
--可以正常运行了
4).删除数据

删除数据和跟新数据类似的,也会报错。需要增加 on delete cascade

小结: 当有外键关联时,被关联列的数据发生改变时,要么该行为被阻止,要么会更新从表中对应的数据

级联更新
ON UPDATE CASCADE
级联删除
ON DELETE CASCADE

当然也可以使用触发器来代替外键

6.check

check 约束在MySQL中目前还不支持

未经允许不得转载:比尔云 » MySQL 数据约束
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

强烈推荐

高性能SSD云服务器ECS抗攻击,高可用云数据库RDS