约束条件

1、null和default

默认情况下，表允许字段为空，并且没有默认值。

如果不允许字段为空，需要添加约束条件 not null。

如果字段有默认值，可以添加约束条件的默认属性 default。

示例：

mysql> create table t5(id int, name char(10) not null, sex enum('male', 'female') not null default 'male');
Query OK, 0 rows affected (0.61 sec)

mysql> desc t5;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int                   | YES  |     | NULL    |       |
| name  | char(10)              | NO   |     | NULL    |       |
| sex   | enum('male','female') | NO   |     | male    |       |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t5(id, name) values(1, 'keyang');  /* 没有指定 sex，sex为默认值‘male’*/
Query OK, 1 row affected (0.35 sec)

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

mysql>

2、唯一性 unique

唯一性使用unique限制。

示例1：单字段唯一

方式一：
mysql> create table t5(id int unique, name char(10) unique);
Query OK, 0 rows affected (1.24 sec)
mysql> desc t5;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  | UNI | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

方式二：
mysql> create table t5(id int, name char(10), unique(id), unique(name));
Query OK, 0 rows affected (1.09 sec)
 line 1
mysql> desc t5;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  | UNI | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

示例2：多字段组合唯一

定义ip和端口号唯一：
mysql> create table service(ip char(15), port int, value char(20), unique(ip, port));
Query OK, 0 rows affected (0.99 sec)

mysql> desc service;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip    | char(15) | YES  | MUL | NULL    |       |
| port  | int      | YES  |     | NULL    |       |
| value | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>mysql> show create table service;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                          |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| service | CREATE TABLE `service` (
  `ip` char(15) DEFAULT NULL,
  `port` int DEFAULT NULL,
  `value` char(20) DEFAULT NULL,
  UNIQUE KEY `ip` (`ip`,`port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

 

3、主键 primary key

注意：对于innodb 存储引擎的表，必须有一个主键(primary key)。

1、单字段主键（单列主键）：

mysql> create table t1(id int, name char(10) primary key);
Query OK, 0 rows affected (0.75 sec)

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | NO   | PRI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

2、复合主键：

mysql> create table service(ip char(15), port int, value char(20), primary key(ip, port));
Query OK, 0 rows affected (0.89 sec)

mysql> desc service;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip    | char(15) | NO   | PRI | NULL    |       |
| port  | int      | NO   | PRI | NULL    |       |
| value | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table service;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                              |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| service | CREATE TABLE `service` (
  `ip` char(15) NOT NULL,
  `port` int NOT NULL,
  `value` char(20) DEFAULT NULL,
  PRIMARY KEY (`ip`,`port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)