Mysql-mysql的约束

发表于   |   分类于 技术 , Mysql   |   暂无评论

示例博客

Mysql-mysql的约束

说明

说明：约束是用于维护数据的完整性, 所谓数据的完整性指的是，就是根据业务逻辑的需要，我们将表的某些字段设置成一种约束，从而保证数据的合理性和完整性，比如身份证编号就是唯一，我们就可以使用unique 这个约束.

约束分类

mysql的约束分类分为5种：
(1)主键约束: primary key
(2)唯一约束: unique
(3)not null
(4)check ：检查约束，很多数据库都有，但是mysql支持语法，并没有实际作用 
(5)外键约束: foreign key

primary key(主键约束)

1.primary key不但不能重复而且不能为null
2.一张表最多只能有一个主键, 但可以是复合主键

 例:primary key(id,name)
 当是复合主键时，两个主键联合满足第一个条件
 insert into zhansgan values(1,'zhangsan');
 insert into zhansgan values(1,'zhangsan');
 这个时候就会报错

3.主键定义有两种

1. id int primary key
2. primary key(id)

unique约束(唯一约束)

当我们希望某个字段的值不能出现重复值时，我们就可以将该字段设置成unique。比如说手机号，身份证号

案例

Create Table: CREATE TABLE `tb2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `phone` char(11) NOT NULL,
  `id_card` char(18) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`),
  UNIQUE KEY `id_card` (`id_card`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> insert into tb2 values(1,'zhangsan','17600128033','371425199503179876');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb2 values(2,'zhangsan','17600128034','371425199503179876');
ERROR 1062 (23000): Duplicate entry '371425199503179876' for key 'id_card'
mysql> insert into tb2 values(2,'zhangsan','17600128033','371425199503179876');
ERROR 1062 (23000): Duplicate entry '17600128033' for key 'phone'
mysql> insert into tb2 values(2,'zhangsan','17600128035',null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb2 values(3,'zhangsan','17600128036',null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb2;
+----+----------+-------------+--------------------+
| id | name     | phone       | id_card            |
+----+----------+-------------+--------------------+
|  1 | zhangsan | 17600128033 | 371425199503179876 |
|  2 | zhangsan | 17600128035 | NULL               |
|  3 | zhangsan | 17600128036 | NULL               |
+----+----------+-------------+--------------------+
3 rows in set (0.00 sec)

结论

**1.被定义为unique的字段不可重复
2.如果我们将某个字段设置为 unique ,但是没有设置 not null, 那么该字段可以为null,并且可以有多个null
3.如果你要某个字段不能重复，而且不能为 null, 我们可以这样定义字段

 字段名 字段类型 not null  unique

4.一个表中，可以有多个unique约束**

外键约束

用于定义主表和从表之间的关系: 外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

创建主从表

创建时的注意点:
**1.语法：foreign key (外键字段) references 主表(字段);
2.表的类型是innodb, 这样的表才支持外键
3.外键指向的主表的字段，要求是primary key 或者是 unique
4.外键字段的类型要和主键/unqiue字段的类型一致(长度可以不同)**

示例：班级、学生表,学生隶属于班级，班级表为主表,学生表为从表
班级表:
CREATE TABLE `classes` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(10) NOT NULL COMMENT '班级名称',
  `c_dec` varchar(255) NOT NULL COMMENT '班级描述',
  PRIMARY KEY (`c_id`),
  UNIQUE KEY `c_name` (`c_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into classes values(1,'1班','1班最棒');
insert into classes values(2,'2班','2班最强');
insert into classes values(3,'3班','3班最闪耀');

学生表:
CREATE TABLE students(
    id INT(11) PRIMARY KEY ,
    c_id INT(11) NOT NULL ,
    s_name VARCHAR(50) NOT NULL ,
    FOREIGN KEY(c_id) REFERENCES classes(c_id)
) charset = utf8 ENGINE = INNODB;
insert into students values (1,1,'小明');
insert into students values (2,2,'小红');
insert into students values (3,3,'小lv');

主从表的数据完整性

1.当给从表添加记录时，要外键的值已经在主表中存在，否则不能添加成功
在学生表中添加4班的小蓝会报错，因为没有4班
mysql> insert into students values (4,4,'小蓝');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `classes` (`c_id`))


2.一旦建立主外键的关系，主表数据不能随意删除和修改了[参照完整性]**
      删除2班时会报错，因为2班已经有人了。
      mysql> delete from classes where c_id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `classes` (`c_id`))

删除约束

1.删除主键约束
alter table tb1 drop primary key;
2.删除not null, unique 
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
alter table tb1 modify name varchar(30);
3.删除外键约束
alter table students drop foreign key c_id;

结尾

腹有诗书气自华，最是书香能致远。

添加新评论