MySQL約束

約束也叫完整性約束(integrity constraint)
什么是完整性？
   完整性是指數(shù)據(jù)庫(kù)中存放的數(shù)據(jù)是有意義的、正確的
什么是約束？
   為了保證數(shù)據(jù)的正確性和相容性，對(duì)關(guān)系模型提出的某些約束條件或者規(guī)則
   
注意：約束一般是作用于字段上的

約束有哪些？
   非空、唯一、默認(rèn)值、主鍵、外鍵、自增
   語(yǔ)法：
       字段名 字段類型 [not null|unique|default 默認(rèn)值|auto_increment]
1、默認(rèn)值
   mysql> create table t6 (name varchar(10),sex char(10) default 'male');
   mysql> insert into t6 values();
   mysql> select * from t6;
       +------+------+
       | name | sex  |
       +------+------+
       | NULL | male |
       +------+------+
       1 row in set (0.00 sec)
  默認(rèn)值：當(dāng)用戶向表中插入數(shù)據(jù)時(shí)，指定了該字段的值，那么就插入該值；否則就插入默認(rèn)值。
   
   修改已經(jīng)存在的表中某個(gè)字段的默認(rèn)值，兩種方法
       alter table 表名 modify 字段名 字段類型 default 默認(rèn)值;
       alter table 表名 alter 字段名 set default 默認(rèn)值;
   mysql> alter table t6 alter name set default 'tom';
   mysql> insert into t6 values();
   mysql> select * from t6;
       +------+------+
       | name | sex  |
       +------+------+
       | NULL | male |
       | tom  | male |
       +------+------+
       2 rows in set (0.00 sec)
2、非空 not null  
   mysql> select * from t6 where name is null;    //查詢name字段為null的行
   mysql> select * from t6 where name is not null;  //查詢name字段不為null的行
   mysql> create table t7 (id int not null,name char(10));
   mysql> insert into t7 values();       //會(huì)將不允許為空的id字段轉(zhuǎn)換成0
   mysql> select * from t7;
       +----+------+
       | id | name |
       +----+------+
       |  0 | NULL |
       |  0 | NULL |
       +----+------+
         2 rows in set (0.00 sec)
   mysql> alter table t7 modify name char(10) not null;  
   mysql> select * from t7;   //字段類型為字符串型，非空約束會(huì)將空值轉(zhuǎn)換為空字符串
       +----+------+
       | id | name |
       +----+------+
       |  0 |      |
       |  0 |      |
       +----+------+
       2 rows in set (0.00 sec)
3、唯一  unique
   mysql> create table t8 (id int unique,name char(10));
   mysql> insert into t8 values();   //注意：唯一性約束對(duì)空值無(wú)效
   mysql> insert into t8 values();
   mysql> select * from t8;
       +------+------+
       | id   | name |
       +------+------+
       | NULL | NULL |
       | NULL | NULL |
       +------+------+
       2 rows in set (0.00 sec)
    mysql> insert into t8 values(1,'tom');
    mysql> insert into t8 values(1,'mary');
       ERROR 1062 (23000): Duplicate entry '1' for key 'id'
   
    mysql> alter table t8 modify name char(10) unique;
    mysql> insert into t8 values(2,'mary');
    mysql> insert into t8 values(3,'tom');
       ERROR 1062 (23000): Duplicate entry 'tom' for key 'name'

4、自增  auto_increment
要求：
   1）該字段必須是數(shù)值型
   2）字段上要有唯一性索引或者主鍵
   mysql> create table t9 (id int primary key auto_increment);
   mysql> desc t9;
       +-------+---------+------+-----+---------+----------------+
       | Field | Type    | Null | Key | Default | Extra          |
       +-------+---------+------+-----+---------+----------------+
       | id    | int(11) | NO   | PRI | NULL    | auto_increment |
       +-------+---------+------+-----+---------+----------------+
       1 row in set (0.00 sec)      
  mysql> insert into t9 values();   //插入1
  mysql> insert into t9 values(3);   //插入3
 
  幾點(diǎn)說(shuō)明：
       1）當(dāng)自增字段發(fā)生斷檔時(shí)，值會(huì)從最大值繼續(xù)自增
       2）當(dāng)delete刪除最大值時(shí)，下一個(gè)值仍然從刪除之前的最大值繼續(xù)自增
       3）當(dāng)truncate表時(shí)，值從1開(kāi)始重新計(jì)算

5、主鍵  primary key
主鍵是表中的特殊字段，這個(gè)字段能夠唯一的標(biāo)識(shí)表中的每一條記錄。
一張表最多只能有一個(gè)主鍵。
主鍵的用途：快速定位數(shù)據(jù)
主鍵需要滿足的條件：非空且唯一
       primary key == not null + unique
1）使用單個(gè)字段做主鍵
   a、在字段后直接指定主鍵約束(列級(jí)約束，默認(rèn)值為NULL)
       mysql> create table t10 (id int primary key,age int,name char(10));
       mysql> desc t10;
           +-------+----------+------+-----+---------+-------+
           | Field | Type     | Null | Key | Default | Extra |
           +-------+----------+------+-----+---------+-------+
           | id    | int(11)  | NO   | PRI | NULL    |       |
           | age   | int(11)  | YES  |     | NULL    |       |
           | name  | char(10) | YES  |     | NULL    |       |
           +-------+----------+------+-----+---------+-------+
       mysql> insert into t10(id) values(1);
       mysql> insert into t10(id) values(1);
           ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
       mysql> insert into t10 values();
       mysql> insert into t10 values();
           ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
    b、整張表的所有字段都定義完成之后再來(lái)指定主鍵(表級(jí)約束，默認(rèn)值是0)
       mysql> create table t11 (id int,name char(5),primary key(id));
       mysql> desc t11;
           +-------+---------+------+-----+---------+-------+
           | Field | Type    | Null | Key | Default | Extra |
           +-------+---------+------+-----+---------+-------+
           | id    | int(11) | NO   | PRI | 0       |       |
           | name  | char(5) | YES  |     | NULL    |       |
           +-------+---------+------+-----+---------+-------+
           2 rows in set (0.00 sec)
       mysql> insert into t11 values();
       mysql> insert into t11 values();
           ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
       mysql> insert into t11 values(1,'hi');
       mysql> select * from t11;
           +----+------+
           | id | name |
           +----+------+
           |  0 | NULL |
           |  1 | hi   |
           +----+------+
           2 rows in set (0.00 sec)
2）多個(gè)字段聯(lián)合做主鍵
   mysql> desc mysql.user \G      //user和host字段聯(lián)合做主鍵
       *************************** 1. row ***************************
         Field: Host
          Type: char(60)
          Null: NO
           Key: PRI
       Default:
         Extra:
       *************************** 2. row ***************************
         Field: User
          Type: char(16)
          Null: NO
           Key: PRI
       Default:
         Extra:
       *************************** 3. row ***************************
         Field: Password
          Type: char(41)
          Null: NO
           Key:
       Default:
         Extra:

  注意：聯(lián)合主鍵只能在所有字段都定義完成之后，才能定義主鍵。
   mysql> create table t12 (id int,name char(2),age int,primary key(id,name));
   mysql> desc t12;
       +-------+---------+------+-----+---------+-------+
       | Field | Type    | Null | Key | Default | Extra |
       +-------+---------+------+-----+---------+-------+
       | id    | int(11) | NO   | PRI | 0       |       |
       | name  | char(2) | NO   | PRI |         |       |
       | age   | int(11) | YES  |     | NULL    |       |
       +-------+---------+------+-----+---------+-------+
   mysql> insert into t12(id) values(1);    
   mysql> select * from t12;
       +----+------+------+
       | id | name | age  |
       +----+------+------+
       |  0 |      | NULL |
       |  1 |      | NULL |
       +----+------+------+
       2 rows in set (0.00 sec)
   mysql> insert into t12(name) values ('a');
   mysql> select * from t12;
       +----+------+------+
       | id | name | age  |
       +----+------+------+
       |  0 |      | NULL |
       |  0 | a    | NULL |
       |  1 |      | NULL |
       +----+------+------+
       3 rows in set (0.00 sec)

6、外鍵  foreign key
   外鍵：一個(gè)表的數(shù)據(jù)依賴于另一張表的主鍵列的數(shù)據(jù)，如果在主鍵列沒(méi)有出現(xiàn)的值，是不能夠出現(xiàn)在外鍵字段的。

  主鍵和外鍵就像粘合劑，能夠?qū)⒍鄠€(gè)表聯(lián)系起來(lái)。

   
創(chuàng)建外鍵的條件：
   1）存儲(chǔ)引擎是innodb
   2）相關(guān)聯(lián)字段數(shù)據(jù)類型要一致
   3）最好在外鍵列上建索引(目的就是為了減小掃描范圍，不創(chuàng)建也可以，只是影響性能)
   
例子：
   dept：部門表
   emp ：?jiǎn)T工表
   mysql> create table dept (dno int,dname char(10),primary key (dno));
   mysql> create table emp (eno int,e_dno int,ename char(15),index(e_dno),foreign key (e_dno) references dept(dno));
向父表中插入數(shù)據(jù)
   mysql> insert into dept values(1,'sa'),(2,'dba'),(3,'manager');
向子表中插入數(shù)據(jù)
   mysql> select * from dept;
       +-----+---------+
       | dno | dname   |
       +-----+---------+
       |   1 | sa      |
       |   2 | dba     |
       |   3 | manager |
       +-----+---------+
       3 rows in set (0.00 sec)

   mysql> insert into emp values(100,3,'Tom');
   mysql> select * from emp;
       +------+-------+-------+
       | eno  | e_dno | ename |
       +------+-------+-------+
       |  100 |     3 | Tom   |
       +------+-------+-------+
       1 row in set (0.00 sec)

   mysql> insert into emp values(101,4,'Mary');   //反例：插入父表中不存在的部門號(hào)
       ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`up1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`e_dno`) REFERENCES `dept` (`dno`))  
       
   mysql> delete from dept where dno=2;
   mysql> delete from dept where dno=3;
       ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`up1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`e_dno`) REFERENCES `dept` (`dno`))
       
  小結(jié)：
     1）子表中的數(shù)據(jù)依賴于父表，不能向子表中插入父表中不存在值
     2）不能刪除父表中被子表所依賴的記錄
     
刪除父表中被依賴的行的方法：
   1）刪除外鍵約束
   2）指定級(jí)聯(lián)操作的選項(xiàng)
   
on delete cascade：級(jí)聯(lián)刪除
on update cascade：級(jí)聯(lián)更新
mysql> drop table emp;
mysql> create table emp (eno int,e_dno int,ename char(15),index(e_dno),foreign key (e_dno) references dept(dno) on delete cascade on update cascade); //完整的外鍵創(chuàng)建
mysql> insert into emp values(100,1,'Tom'),(101,3,'Mary'),(103,1,'Jack');
mysql> select * from dept;
   +-----+---------+
   | dno | dname   |
   +-----+---------+
   |   1 | sa      |
   |   3 | manager |
   +-----+---------+
   2 rows in set (0.00 sec)

mysql> select * from emp;
   +------+-------+-------+
   | eno  | e_dno | ename |
   +------+-------+-------+
   |  100 |     1 | Tom   |
   |  101 |     3 | Mary  |
   |  103 |     1 | Jack  |
   +------+-------+-------+
   3 rows in set (0.00 sec)

mysql> delete from dept where dno=1;
   Query OK, 1 row affected (0.02 sec)

mysql> select * from emp;
   +------+-------+-------+
   | eno  | e_dno | ename |
   +------+-------+-------+
   |  101 |     3 | Mary  |
   +------+-------+-------+
   1 row in set (0.00 sec)

mysql> update dept set dno=100 where dno=3;
   Query OK, 1 row affected (0.07 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

 mysql> select * from emp;
   +------+-------+-------+
   | eno  | e_dno | ename |
   +------+-------+-------+
   |  101 |   100 | Mary  |
   +------+-------+-------+
   1 row in set (0.00 sec)
       有了級(jí)聯(lián)刪除和級(jí)聯(lián)修改選項(xiàng)，父表中的數(shù)據(jù)發(fā)生刪除或者更新時(shí)，子表中相關(guān)數(shù)據(jù)也會(huì)發(fā)生相應(yīng)的變化。

刪除外鍵
   alter table 表名 drop foreign key 外鍵的名字
   mysql> show create table emp \G   //紅色字體為外鍵的名字
*************************** 1. row ***************************
      Table: emp
Create Table: CREATE TABLE `emp` (
 `eno` int(11) DEFAULT NULL,
 `e_dno` int(11) DEFAULT NULL,
 `ename` char(15) DEFAULT NULL,
 KEY `e_dno` (`e_dno`),
 CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`e_dno`) REFERENCES `dept` (`dno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> alter table emp drop foreign key emp_ibfk_1;
mysql> show create table emp \G
*************************** 1. row ***************************
      Table: emp
Create Table: CREATE TABLE `emp` (
 `eno` int(11) DEFAULT NULL,
 `e_dno` int(11) DEFAULT NULL,
 `ename` char(15) DEFAULT NULL,
 KEY `e_dno` (`e_dno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)