约束条件
目录
关于key的问题:
primary key不为空,且唯一not null unique
如果不指定主键(primary key), innodb会自己找第一个不为空且唯一的字段为主键,如果没有,则会创建隐藏的字段为主键.
- 一张表只有一个?
- 为何innodb必须有一个主键?
- 干什么用?
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
UNSIGNED 无符号
age int unsigned NOT NULL #无符号的正值,不允许为空
not null (default) #标识该字段不能为空
sex enum('male','female') not null default 'male' #性别不允许为空,且默认为男性
unique 唯一
几种创建唯一值得写法
tcc@localhost [db1] > create table t1(id int unique,name varchar(30));
Query OK, 0 rows affected (0.02 sec)
tcc@localhost [db1] > create table t1
-> (id int,
-> name varchar(30),
-> constraint uniq_name unique(id)); #也可交简写成 'unique(id)'
Query OK, 0 rows affected (0.02 sec)
tcc@localhost [db1] > desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
tcc@localhost [db1] > show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
auto_increment 自增长
约束字段为自动增长，被约束的字段必须同时被key约束
tcc@localhost [db2] > create table t3(id int primary key auto_increment);
Query OK, 0 rows affected (0.01 sec)
tcc@localhost [db2] > show create table t3;
......
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
创建表后修改自增长步chang
tcc@localhost [db2] > alter table t3 auto_increment=3;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
tcc@localhost [db2] > show create table t3;
...
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
#另一种写法
#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外:
tcc@localhost [db2] > create table t34(
-> id int primary key auto_increment
-> )auto_increment=3;
测试:
tcc@localhost [db2] > insert into t3 values();
Query OK, 1 row affected (0.00 sec)
tcc@localhost [db2] > insert into t3 values();
Query OK, 1 row affected (0.00 sec)
tcc@localhost [db2] > insert into t3 values();
Query OK, 1 row affected (0.00 sec)
tcc@localhost [db2] > select * from t3;
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
+----+
3 rows in set (0.00 sec)
设置步长
查询
# 查询会话
tcc@localhost [db2] > show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
# 查询全局
tcc@localhost [db2] > show global variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
auto_increment_offset 初始偏移量
auto_increment_increment 步长
- 注意:
-
如果初始值一定小于等于步长,否则初始值将会被忽略.
-
设置全局后,需重新登录数据库后生效.
-
修改:
# 修改会话级别
set session auto_increment_increment=3;
#修改全局级别
set global auto_increment_increment=3;
联合唯一
- 联合主键
- 联合唯一
联合唯一示例:
tcc@localhost [db2] > create table service(
-> id int primary key auto_increment,
-> host char(32),
-> port int,
-> unique(host,port)
-> );
Query OK, 0 rows affected (0.02 sec)
指定主机跟端口联合唯一.
