约束
约束可以保证记录的唯一性,即就是同一个表中,相同字段的值不会出现重复。
1.主键约束
创建主键约束的user表格,约束id
mysql> create table user(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.14 sec)```
mysql> insert into user values(1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
插入id为1,name为张三的数据,第一次成功,而第二次会报错
这时候因为我们约束的是id,id必须为唯一值,这时候只要id改成2,就可以插入了
mysql> insert into user values(2,'张三');
Query OK, 1 row affected (0.01 sec)
select * from user;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 张三 |
+----+--------+
2 rows in set (0.00 sec)
mysql> insert into user values(1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
这时候我们插入了id为1,name为李四的数据,也会报错。
2.联合主键
只要联合的主键值加起来不重复就可以了
mysql> create table user2(
id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);
mysql> insert into user2 values(1,'张三','123');
mysql> insert into user2 values(2,'张三','123');
mysql> insert into user2 values(1,'李四','123');
mysql> select * from user2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 张三 | 123 |
| 1 | 李四 | 123 |
| 2 | 张三 | 123 |
+----+--------+----------+
3 rows in set (0.00 sec)
这时候id不需要为唯一值
3.自增约束
自动生成
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into user3 (name) values('张三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user3;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
+----+--------+
1 row in set (0.00 sec)
AUTO_INCREMENT可以约束任何一个字段,该字段不一定是PRIMARY KEY字段,也就是说自增的字段并不等于主键字段。
4.唯一约束
唯一约束的值可以为空,但是不能与其他的值重复
mysql> create table user6(
-> id int,
-> name varchar(20),
-> unique(name));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into user6 values(1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user6 values(2,'张三');
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'
mysql> insert into user6 values(1,'');
Query OK, 1 row affected (0.01 sec)
5.默认约束
对表中的某个设置默认值
mysql> create table user10(
-> id int,
-> name varchar(20),
-> age int default 10);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into user10 (id, name) values(1,'张三');
Query OK, 0 rows affected (0.10 sec)
+----+--------+------+
| id | name |age |
+----+--------+-------+
| 1 | 张三 | 10 |
+----+--------+-------+