mysql 基础--约束

主键约束

  1 在创建主键时候如果有auto_increment关键字，那么必须设置为主键，否则报错

 

mysql> create table t2(
    -> id smallInt unsigned auto_increment,
    -> username varchar(30) not null
    -> );
1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

    设置成主键后

   

mysql> create table t2(
    -> id smallInt unsigned auto_increment primary key,
    -> username varchar(30)
    -> );
Database changed

 

 

   显示字段的详细信息

  

mysql> show columns from t2;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)          | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set

 唯一约束

  

mysql> create table t3(
    -> username varchar(20) unique key);
Database changed
mysql> show columns from t3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  | UNI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
1 row in set

mysql> insert into t3 values('zhang');
Query OK, 1 row affected

mysql> insert into t3 values('zhang');
1062 - Duplicate entry 'zhang' for key 1

  添加的空的话会不会唯一呢？

  

mysql> insert into t3 values();
Query OK, 1 row affected

mysql> insert into t3 values();
Query OK, 1 row affected

mysql> insert into t3 values();
Query OK, 1 row affected

mysql> select username from t3;
+----------+
| username |
+----------+
| NULL     |
| NULL     |
| NULL     |
| NULL     |
| zhang    |
+----------+

 默认约束，没有值的情况下的约束

  

mysql> create table t5(
    -> username varchar(30) default 'zhangsan'
    -> );
mysql> insert into t5 values();
Query OK, 1 row affected
mysql> select username from t5;
+----------+
| username |
+----------+
| zhangsan |
+----------+
1 row in set

 非空约束

   

mysql> create table t6(
    -> username varchar(20) not null);
Database changed
mysql> insert into t6 values();
1364 - Field 'username' doesn't have a default value

 

  外键约束 PROEIGN KEY

  1保持数据一致性，实现一对一或者一对多的关系

  2要求 父表子表必须使用相同的存储引擎 innoDB ,default -storage-engine=INNODB 

  3禁止使用临时表，必须据说相似的数据类型

  4 外键列跟参照列必须创建索引

  

mysql> create table dept(
    -> id smallint auto_increment primary key,
    -> deptName varchar(20));

    

mysql> create table user(
    -> uid smallint auto_increment primary key,
    -> username varchar(20),
    -> deptId smallint,
    -> FOREIGN KEY(deptId) REFERENCES dept(id));
Database changed

   查索引命令用show index from user;

 

　cascade:从父表删除或更新且自动删除或更新子表中匹配的行

　set null :从父表删除或更新行，并设置子表中的外键列为NULL。如果石永红该选项，必须保证子表列没有指定NOT NULL

    restrict：拒绝对父表删除或更新操作

　not action:标准sql关键字,在mysql中与restrict相同

 

 

 

  约束中的cascade 级联删除  父表删除，子表对应的也就删除了   on delete cascade

                cascade 级联修改  父表修改，子表对应的也就修改了   on  update cascade

mysql> create table dept(
    -> id smallInt auto_increment primary key,
    -> deptName varchar(20) not null);
Query OK, 0 rows affected

mysql> create table user(
    -> id smallInt auto_increment primary key,
    -> username varchar(20) not null,
    -> pid smallInt,
    -> foreign key (pid) references dept(id) on delete cascade);
Database changed
mysql> insert into dept(deptName) values('测试部');
Query OK, 1 row affected

mysql> insert into dept(deptName) values('研发部');
Query OK, 1 row affected

mysql> insert into dept(deptName) values('财务部');
Query OK, 1 row affected

mysql> insert into dept(deptName) values('安全部');
Query OK, 1 row affected

mysql> select * from dept;
+----+----------+
| id | deptName |
+----+----------+
|  4 | 测试部   |
|  5 | 研发部   |
|  6 | 财务部   |
|  7 | 安全部   |
+----+----------+
mysql> insert into user(username,pid)values('zhangsan',1);
1452 - Cannot add or update a child row: a foreign key constraint fails (`t1/user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `dept` (`id`) ON DELETE CASCADE)
mysql> insert into user(username,pid) values('zhangsang',4);
Database changed
mysql> insert into user(username,pid) values('lisi',5);
Database changed
mysql> insert into user(username,pid) values('wangwu',6);
Database changed
mysql> insert into user(username,pid) values('zxg',8);
1452 - Cannot add or update a child row: a foreign key constraint fails (`t1/user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `dept` (`id`) ON DELETE CASCADE)

mysql> select * from user;
+----+-----------+-----+
| id | username  | pid |
+----+-----------+-----+
|  4 | zhangsang |   4 |
|  5 | lisi      |   5 |
|  6 | wangwu    |   6 |
+----+-----------+-----+
3 rows in set

 

 

mysql> delete from user where id=6;
Database changed
mysql> select * from user;
+----+-----------+-----+
| id | username  | pid |
+----+-----------+-----+
|  4 | zhangsang |   4 |
|  5 | lisi      |   5 |
+----+-----------+-----+
2 rows in set

 

mysql> delete from dept where id =4;
Query OK, 1 row affected

mysql> select * from dept;
+----+----------+
| id | deptName |
+----+----------+
|  5 | 研发部   |
|  6 | 财务部   |
|  7 | 安全部   |
+----+----------+
3 rows in set

mysql> select * from user;
+----+----------+-----+
| id | username | pid |
+----+----------+-----+
|  5 | lisi     |   5 |
+----+----------+-----+
1 row in set

   

mysql> update dept set id=8 where id=5;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`t1/user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `dept` (`id`) ON DELETE CASCADE)

 

   

mysql> create table dept(
    -> id smallInt auto_increment primary key,
    -> deptName varchar(20) not null);
Query OK, 0 rows affected

mysql> create table user(
    -> id smallInt auto_increment primary key,
    -> username varchar(20) not null,
    -> pid smallInt,
    -> foreign key (pid) references dept(id) on update cascade);

mysql> insert into dept(deptName)values('测试部');
Query OK, 1 row affected

mysql> insert into dept(deptName)values('研发部');
Query OK, 1 row affected

mysql> insert into dept(deptName) values('安全部');
Query OK, 1 row affected

mysql> select * from dept;
+----+----------+
| id | deptName |
+----+----------+
|  1 | 测试部   |
|  2 | 研发部   |
|  3 | 安全部   |
mysql> insert into user(username,pid) values('张三',1);
Database changed
mysql> insert into user(username,pid) values('李四',2);
Database changed
mysql> select * from user;
+----+----------+-----+
| id | username | pid |
+----+----------+-----+
|  1 | 张三     |   1 |
|  2 | 李四     |   2 |
+----+----------+-----+
2 rows in set
mysql> delete from dept where id=2;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (`t1/user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `dept` (`id`) ON UPDATE CASCADE)
mysql> update dept set id=4 where id=2;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

 

   同理　set null 分on delete set null 和 on update set null

mysql>  create table user(
    id smallInt auto_increment primary key,
    username varchar(20),
    pid smallInt,
    foreign key(pid) references dept(id) on delete set null);
Database changed

 

mysql> insert into dept(deptName)values('测试部');
Query OK, 1 row affected

mysql> insert into dept(deptName) values('安全部');
Query OK, 1 row affected

mysql> select * from user;
Empty set

mysql> select * from dept;
+----+----------+
| id | deptName |
+----+----------+
|  1 | 测试部   |
|  2 | 安全部   |
+----+----------+
2 rows in set

mysql> insert into user(username,pid)values('张三',1);
Database changed
mysql> select * from user;
+----+----------+-----+
| id | username | pid |
+----+----------+-----+
|  1 | 张三     |   1 |
+----+----------+-----+
1 row in set

mysql> delete from dept where id=1;
Query OK, 1 row affected

mysql> select * from user;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | 张三     | NULL |
+----+----------+------+
1 row in set

  其他就不多说了，