mysql是如何保证三类完整性约束的_Mysql---完整性约束

完整性约束---用于保证数据的完整性和一致性

KEY POINT:

not null 与 default

unique

primary key

auto_increment

foreign key

一 not null 与 default

1.是否可空

null空

not null不可空

2.默认值default,当不设置时，默认值为null,当设置默认值时，默认值为设置的默认值

a.默认值可以为空:

b.设置not null，插入值时不能为空

c.设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

二 unique

1.单列唯一

2.联合唯一

3.组合唯一

三 primary key

1.主键  相当于 not null +unique  字段的值唯一且不为空

mysql>create table t10(-> id intprimary key,-> name char(16)->);

Query OK,0 rows affected (0.04sec)

mysql>insert into t10 values-> (1,"xiaoma"),-> (2,"alex"),-> (3,"wusir");

Query OK,3 rows affected (0.01sec)

Records:3 Duplicates: 0 Warnings: 0mysql> select * fromt10;+----+--------+

| id | name |

+----+--------+

| 1 | xiaoma |

| 2 | alex |

| 3 | wusir |

+----+--------+

3 rows in set (0.00sec)

mysql>insert into t10 values-> (1,"tailiang");

ERROR1062 (23000): Duplicate entry ‘1‘ for key ‘PRIMARY‘mysql>desc t10;+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | char(16) | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

View Code

2.一个表中可以有单列做主键，也可以有多列做主键

mysql>create table t12(-> ip char(15),-> port int,->primary key(ip,port)->);

Query OK,0 rows affected (0.05 sec)

View Code

3.存储引擎默认是(innodb):对于innodb存储引擎来说，一张表必须有一个主键。

四 auto_increment  约束的字段为自动增长，约束的字段必须同时被key约束

1.不指定id,则自动增长

mysql>create table t14->(-> id intprimary key auto_increment,-> name varchar(15) not null);

Query OK,0 rows affected (0.05sec)

mysql> insert into t14 values ("太白"),("太黑");

ERROR1136 (21S01): Column count doesn‘t match value count at row 1

mysql> insert into t14(name) values ("太白"),("太黑");

Query OK,2 rows affected (0.00sec)

Records:2 Duplicates: 0 Warnings: 0mysql> select * fromt14;+----+--------+

| id | name |

+----+--------+

| 1 | 太白 |

| 2 | 太黑 |

+----+--------+

2 rows in set (0.00 sec)

View Code

2.也可指定id

3.对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

4.注意：

a.步长auto_increment_increment,默认为1

b.起始的偏移量auto_increment_offset, 默认是1

可通过设置以上两个值改变默认的步长和起始偏移量

mysql>use db1

Database changed

#创建表t16

mysql>create table t16(-> id intprimary key auto_increment,-> name varchar(10) not null

->);

Query OK,0 rows affected (0.04sec)

#插入数据

mysql>insert into t16 values-> (1,"alex"),-> (2,"wusir"),-> (3,"egon"),-> (4,"xiaomage");

Query OK,4 rows affected (0.01sec)

Records:4 Duplicates: 0 Warnings: 0#显示t16详细数据

mysql> select * fromt16;+----+----------+

| id | name |

+----+----------+

| 1 | alex |

| 2 | wusir |

| 3 | egon |

| 4 | xiaomage |

+----+----------+

4 rows in set (0.00sec)

#删除 id=2的字段记录

mysql> delete from t16 where id=2;

Query OK,1 row affected (0.00sec)

mysql> select * fromt16;+----+----------+

| id | name |

+----+----------+

| 1 | alex |

| 3 | egon |

| 4 | xiaomage |

+----+----------+

3 rows in set (0.00sec)

#删除 id=4的字段记录

mysql> delete from t16 where id=4;

Query OK,1 row affected (0.01sec)

mysql> select * fromt16;+----+------+

| id | name |

+----+------+

| 1 | alex |

| 3 | egon |

+----+------+

2 rows in set (0.00sec)

#插入新的纪录

mysql>insert into t16 values-> ("haha");

ERROR1136 (21S01): Column count doesn‘t match value count at row 1

mysql>insert into t16(name) values-> ("haha");

Query OK,1 row affected (0.01sec)

mysql> select * fromt16;+----+------+

| id | name |

+----+------+

| 1 | alex |

| 3 | egon |

| 5 | haha |#新插入的字段依然按照之前顺序排列+----+------+

3 rows in set (0.00sec)

#查看可用的 开头auto_inc的词

mysql> show variables like ‘auto_inc%‘;+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 1 |#步长默认为1| auto_increment_offset | 1 |#偏移量默认为1+--------------------------+-------+

2 rows in set, 1 warning (0.02sec)

mysql> set session auto_increment_increment=5; #设置步长为5

Query OK,0 rows affected (0.01sec)

mysql> set session auto_increment_offset=2; #设置偏移量为2

Query OK,0 rows affected (0.00sec)

#确认设置成功

mysql> show variables like ‘auto_inc%‘;+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| auto_increment_increment | 5 |

| auto_increment_offset | 2 |

+--------------------------+-------+

2 rows in set, 1 warning (0.00sec)

#重新插入新纪录

mysql>insert into t16(name) values-> ("灌灌灌灌");

Query OK,1 row affected (0.00sec)

#查看新纪录id

mysql> select * fromt16;+----+--------------+

| id | name |

+----+--------------+

| 1 | alex |

| 3 | egon |

| 5 | haha |

| 7 | 灌灌灌灌 |

+----+--------------+

4 rows in set (0.00sec)

mysql>insert into t16(name) values-> ("丰富");

Query OK,1 row affected (0.01sec)

mysql> select * fromt16;+----+--------------+

| id | name |

+----+--------------+

| 1 | alex |

| 3 | egon |

| 5 | haha |

| 7 | 灌灌灌灌 |

| 12 | 丰富 |

+----+--------------+

5 rows in set (0.00 sec)

View Code

五 foreign key

六 delete 和 truncate的区别

原文：https://www.cnblogs.com/wdbgqq/p/9555172.html