当前位置：Gxlcms > 数据库问题 > mysql—表的完整性约束

mysql—表的完整性约束

时间：2021-07-01 10:21:17 帮助过：26人阅读

  • 　　not null
  • 　　unique
  • 　　primary key
  • 　　foreign key
    • 返回顶部

    概览

    　　为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。 

    　　约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

    # NOT NULL ：非空约束，指定某列不能为空； 
# UNIQUE : 唯一约束，指定某列或者几列组合不能重复
# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
    返回顶部

    NOT NULL

    是否可空，null表示空，非字符串
    not null - 不可空
    null - 可空 

    技术图片 
    mysql> create table t12 (id int not null);
Query OK, 0 rows affected (0.02 sec)

    mysql> select * from t12;
    
Empty     set (0.00 sec)
    

    mysql> desc t12;
    
    +-------+---------+------+-----+---------+-------+
    
| Field | Type    | Null | Key | Default | Extra |
    
+-------+---------+------+-----+---------+-------+
    
| id    | int(11) | NO   |     | NULL    |       |
    
+-------+---------+------+-----+---------+-------+
    
1 row in set (0.00 sec)
    

    不能向id列插入空元素。
    

    mysql> insert into t12 values (null);
    
ERROR     1048 (23000): Column ‘id‘ cannot be null
    

    mysql> insert into t12 values (1);
    
Query OK,     1 row affected (0.01 sec)

    not null示例

    DEFAULT

    我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

    默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

    技术图片 
    mysql> create table t13 (id1 int not null,id2 int not null default 222);
Query OK, 0 rows affected (0.01 sec)

    mysql> desc t13;
    
    +-------+---------+------+-----+---------+-------+
    
| Field | Type    | Null | Key | Default | Extra |
    
+-------+---------+------+-----+---------+-------+
    
| id1   | int(11) | NO   |     | NULL    |       |
    
| id2   | int(11) | NO   |     | 222     |       |
    
+-------+---------+------+-----+---------+-------+
    
2 rows in set (0.01 sec)
    

    只向id1字段添加值，会发现id2字段会使用默认值填充
    

    mysql> insert into t13 (id1) values (111);
    
Query OK,     1 row affected (0.00 sec)
    

    mysql> select * from t13;
    
    +-----+-----+
    
| id1 | id2 |
    
+-----+-----+
    
| 111 | 222 |
    
+-----+-----+
    
1 row in set (0.00 sec)
    

    id1字段不能为空，所以不能单独向id2字段填充值；
    

    mysql> insert into t13 (id2) values (223);
    
ERROR     1364 (HY000): Field ‘id1‘ doesn‘t have a default value
    

    向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
    

    mysql> insert into t13 (id1,id2) values (112,223);
    
Query OK, 1 row affected (0.00 sec)
    

    mysql> select * from t13;
    
+-----+-----+
    
| id1 | id2 |
    
+-----+-----+
    
| 111 | 222 |
    
| 112 | 223 |
    
+-----+-----+
    
2 rows in set (0.00 sec)

    not null + default 示例 技术图片 
    设置严格模式：
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

    直接在mysql中生效(重启失效):
    
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
    

    配置文件添加(永久失效)：
    
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    not null不生效 返回顶部

    UNIQUE

    唯一约束，指定某列或者几列组合不能重复

    技术图片 
    方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);

    方法二：
    
create table department2(
    
id     int,
    
name     varchar(20),
    
comment     varchar(100),
    
    unique(name)
    
);
    

    mysql> insert into department1 values(1,‘IT‘,‘技术‘);
    
Query OK,     1 row affected (0.00 sec)
    
mysql    > insert into department1 values(1,‘IT‘,‘技术‘);
    
ERROR     1062 (23000): Duplicate entry ‘IT‘ for key ‘name‘

    unique示例 技术图片 
    mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

    mysql> desc t1;
    
    +-------+---------+------+-----+---------+-------+
    
| Field | Type    | Null | Key | Default | Extra |
    
+-------+---------+------+-----+---------+-------+
    
| id    | int(11) | NO   | PRI | NULL    |       |
    
+-------+---------+------+-----+---------+-------+
    
1 row in set (0.00 sec)

    not null 和unique的结合 技术图片 
    create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

    mysql> insert into service values
    
-> (1,‘nginx‘,‘192.168.0.10‘,80),
    
    -> (2,‘haproxy‘,‘192.168.0.20‘,80),
    
    -> (3,‘mysql‘,‘192.168.0.30‘,3306)
    
    -> ;
    
Query OK,     3 rows affected (0.01 sec)
    
Records:     3  Duplicates: 0  Warnings: 0
    

    mysql> insert into service(name,host,port) values(‘nginx‘,‘192.168.0.10‘,80);
    
ERROR     1062 (23000): Duplicate entry ‘192.168.0.10-80‘ for key ‘host‘

    联合唯一 返回顶部

    PRIMARY KEY

    主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
    主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
    主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
    主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

    1.单字段主键

    技术图片 
    ============单列做主键===============
#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

    mysql> desc department1;
    
    +---------+--------------+------+-----+---------+-------+
    
| Field   | Type         | Null | Key | Default | Extra |
    
+---------+--------------+------+-----+---------+-------+
    
| id      | int(11)      | NO   | PRI | NULL    |       |
    
| name    | varchar(20)  | NO   | UNI | NULL    |       |
    
| comment | varchar(100) | YES  |     | NULL    |       |
    
+---------+--------------+------+-----+---------+-------+
    
rows in set (0.01 sec)
    

    方法二：在某一个字段后用primary key
    

    create table department2(
    
id     int primary key, #主键
    
name     varchar(20),
    
comment     varchar(100)
    
);
    

    mysql> desc department2;
    
    +---------+--------------+------+-----+---------+-------+
    
| Field   | Type         | Null | Key | Default | Extra |
    
+---------+--------------+------+-----+---------+-------+
    
| id      | int(11)      | NO   | PRI | NULL    |       |
    
| name    | varchar(20)  | YES  |     | NULL    |       |
    
| comment | varchar(100) | YES  |     | NULL    |       |
    
+---------+--------------+------+-----+---------+-------+
    
rows in set (0.00 sec)
    

    方法三：在所有字段后单独定义primary key
    

    create table department3(
    
id     int,
    
name     varchar(20),
    
comment     varchar(100),
    
    primary key(id); #创建主键并为其命名pk_name
    

    mysql> desc department3;
    
    +---------+--------------+------+-----+---------+-------+
    
| Field   | Type         | Null | Key | Default | Extra |
    
+---------+--------------+------+-----+---------+-------+
    
| id      | int(11)      | NO   | PRI | NULL    |       |
    
| name    | varchar(20)  | YES  |     | NULL    |       |
    
| comment | varchar(100) | YES  |     | NULL    |       |
    
+---------+--------------+------+-----+---------+-------+
    
rows in set (0.01 sec)
    

    方法四：给已经建成的表添加主键约束
    

    mysql> create table department4(
    
    -> id int,
    
    -> name varchar(20),
    
    -> comment varchar(100));
    
Query OK,     0 rows affected (0.01 sec)
    

    mysql> desc department4;
    
    +---------+--------------+------+-----+---------+-------+
    
| Field   | Type         | Null | Key | Default | Extra |
    
+---------+--------------+------+-----+---------+-------+
    
| id      | int(11)      | YES  |     | NULL    |       |
    
| name    | varchar(20)  | YES  |     | NULL    |       |
    
| comment | varchar(100) | YES  |     | NULL    |       |
    
+---------+--------------+------+-----+---------+-------+
    
3 rows in set (0.01 sec)
    

    mysql> alter table department4 modify id int primary key;
    
Query OK,     0 rows affected (0.02 sec)
    
Records:     0  Duplicates: 0  Warnings: 0
    

    mysql> desc department4;
    
    +---------+--------------+------+-----+---------+-------+
    
| Field   | Type         | Null | Key | Default | Extra |
    
+---------+--------------+------+-----+---------+-------+
    
| id      | int(11)      | NO   | PRI | NULL    |       |
    
| name    | varchar(20)  | YES  |     | NULL    |       |
    
| comment | varchar(100) | YES  |     | NULL    |       |
    
+---------+--------------+------+-----+---------+-------+
    
3 rows in set (0.01 sec)

    单字段主键

    2.多字段主键

    技术图片 
    ==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);

    mysql> desc service;
    
    +--------------+-------------+------+-----+---------+-------+
    
| Field        | Type        | Null | Key | Default | Extra |
    
+--------------+-------------+------+-----+---------+-------+
    
| ip           | varchar(15) | NO   | PRI | NULL    |       |
    
| port         | char(5)     | NO   | PRI | NULL    |       |
    
| service_name | varchar(10) | NO   |     | NULL    |       |
    
+--------------+-------------+------+-----+---------+-------+
    
3 rows in set (0.00 sec)
    

    mysql> insert into service values
    
-> (‘172.16.45.10‘,‘3306‘,‘mysqld‘),
    
    -> (‘172.16.45.11‘,‘3306‘,‘mariadb‘)
    
    -> ;
    
Query OK,     2 rows affected (0.00 sec)
    
Records:     2  Duplicates: 0  Warnings: 0
    

    mysql> insert into service values (‘172.16.45.10‘,‘3306‘,‘nginx‘);
    
ERROR     1062 (23000): Duplicate entry ‘172.16.45.10-3306‘ for key ‘PRIMARY‘

    多字段主键

    AUTO_INCREMENT

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

    技术图片 
    #不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum(‘male‘,‘female‘) default ‘male‘
);

    mysql> desc student;
    
    +-------+-----------------------+------+-----+---------+----------------+
    
| Field | Type                  | Null | Key | Default | Extra          |
    
+-------+-----------------------+------+-----+---------+----------------+
    
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
    
| name  | varchar(20)           | YES  |     | NULL    |                |
    
| sex   | enum(‘male‘,‘female‘) | YES  |     | male    |                |
    
+-------+-----------------------+------+-----+---------+----------------+
    
mysql> insert into student(name) values
    
-> (‘egon‘),
    
    -> (‘alex‘)
    
    -> ;
    

    mysql> select * from student;
    
    +----+------+------+
    
| id | name | sex  |
    
+----+------+------+
    
|  1 | egon | male |
    
|  2 | alex | male |
    
+----+------+------+
    

    

    也可以指定id
    

    mysql> insert into student values(4,‘asb‘,‘female‘);
    
Query OK,     1 row affected (0.00 sec)
    

    mysql> insert into student values(7,‘wsb‘,‘female‘);
    
Query OK,     1 row affected (0.00 sec)
    

    mysql> select * from student;
    
    +----+------+--------+
    
| id | name | sex    |
    
+----+------+--------+
    
|  1 | egon | male   |
    
|  2 | alex | male   |
    
|  4 | asb  | female |
    
|  7 | wsb  | female |
    
+----+------+--------+
    

    

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

    mysql> delete from student;
    
Query OK,     4 rows affected (0.00 sec)
    

    mysql> select * from student;
    
Empty     set (0.00 sec)
    

    mysql> insert into student(name) values(‘ysb‘);
    
mysql    > select * from student;
    
    +----+------+------+
    
| id | name | sex  |
    
+----+------+------+
    
|  8 | ysb  | male |
    
+----+------+------+
    

    

    应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
    

    mysql> truncate student;
    
Query OK,     0 rows affected (0.01 sec)
    

    mysql> insert into student(name) values(‘egon‘);
    
Query OK,     1 row affected (0.01 sec)
    

    mysql> select * from student;
    
    +----+------+------+
    
| id | name | sex  |
    
+----+------+------+
    
|  1 | egon | male |
    
+----+------+------+
    
row in set (0.00 sec)

    设置auto_increment

    了解知识

    技术图片 
    #在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum(‘male‘,‘female‘)

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！