mysql学习笔记二 约束

mysql建表约束

主键约束
它能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束，就可以使的该字段不重复且不为空。

mysql> create table user(
    -> id int primary key,
    -> name varchar(20)

mysql> insert into user values(1,'zs');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(1,'zs');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into user values(2,'zs');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(NULL,'zs');
ERROR 1048 (23000): Col
umn 'id' cannot be null

id有主键约束，所以不能添加重复的，也不能为空。

联合主键（两个加起来不重复就可以）
primary key(id,name)

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> passwd varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user2 values(1,'zs','123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user2 values(1,'zs','123');
ERROR 1062 (23000): Duplicate entry '1-zs' for key 'PRIMARY'
mysql> insert into user2 values(2,'zs','123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user2 values(1,'ls','123');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user2 values(1,NULL,'123');
ERROR 1048 (23000): Column 'name' cannot be null

联合主键里面任何一个字段不能为空

自增约束
自动生成id，id自动增长

mysql> insert into user3 (name) values('zs');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
+----+------+
1 row in set (0.00 sec)
mysql> insert into user3 (name) values('zs');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | zs   |
+----+------+
2 rows in set (0.00 sec)

创建表的时候忘记创建主键约束了怎么办？用alter修改表结构，添加主键。

mysql> create table user4( id int, name varchar(20) );

mysql> alter table user4 add primary key(id);

删除主键：

mysql> alter table user4 drop primary key;

修改主键约束：使用modify修改约束添加字段。

mysql> alter table user4 modify id int primary key;

唯一约束
约束修饰的字段的值不可以重复

mysql> create table user5( id int, name varchar(20) );
mysql> alter table user5 add unique(name);
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> insert into user5 values(1,'zs');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user5 values(1,'zs');
ERROR 1062 (23000): Duplicate entry 'zs' for key 'name'
mysql> insert into user5 values(2,'zs');
ERROR 1062 (23000): Duplicate entry 'zs' for key 'name'
mysql> insert into user5 values(2,'ls');
Query OK, 1 row affected (0.00 sec)

mysql> create table user6( id int, name varchar(20),unique(name));
mysql> create table user7( id int, name varchar(20) unique);

使用modify添加约束

mysql> create table user7( id int, name varchar(20));
mysql> alter table user7 modify name varchar(20) unique;

以上四个实现功能一样（user5，user6，user7）

约束两个，这两个字段都不能重复（组合在一起不重复就行）

mysql> create table user8( id int, name varchar(20),unique(id,name));

删除唯一约束

mysql> alter table user7 drop index name;

添加约束方式：
1.建表的时候添加约束
2.建表完后使用 alter…add…
3.建表完后使用 alter…modify…

非空约束-------修饰的字段不能为空

mysql> create table user9( id int, name varchar(20) not null);

mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

name字段不能为空

默认约束-----当我们插入字段值的时候如果没有传值就会使用默认值

mysql> create table user10( id int, name varchar(20),age int default 10);

mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+

插入数据

mysql> insert into user10 (id,name) values(1,'zs');
mysql> select * from user10;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zs   |   10 |
+------+------+------+

mysql> insert into user10 values(1,'zs',19);
mysql> select * from user10;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zs   |   10 |
|    1 | zs   |   19 |
+------+------+------+

没写值自动填默认值，写了就写了呗

外键约束
涉及两个表：父表，子表或者是主表、副表

mysql> create table classes( id int primary key, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create table students( id int primary key, name varchar(20),class_id int,foreign key(class_id) references classes(id));

mysql> insert into classes values(1,'yi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(2,'er');
mysql> insert into classes values(3,'san');
mysql> insert into classes values(4,'si');

mysql> select * from classes;
+----+------+
| id | name |
+----+------+
|  1 | yi   |
|  2 | er   |
|  3 | san  |
|  4 | si   |
+----+------+

mysql> insert into students values(1001,'zs',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1002,'zs',2);
mysql> insert into students values(1003,'zs',3);
mysql> insert into students values(1004,'zs',4);

mysql> insert into students values(1005,'zs',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

主表里不存在的副表不能使用
主表中的记录被副表引用，是不能被删除的。

mysql> delete from classes where id=4;
ERROR 1451 (23000)...