mysql建表约束
-
主键约束
他能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束，就可以使的该字段不重复且不为空
create table user(
id int primary key,
name varchar(20)
);
输入 describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
这里的key值下的PRI即为主键约束
我们可以插入一个记录
insert into user values(1,'张三');
再插入一次相同的记录
insert into user values(1,'张三');
报错-------，是因为key值为1已经固定，不能再次插入key为1的记录,也不能为空
输入select * from user;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
+----+------+
还有一种情况，成为联合主键
> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id,name) //表示id 和name 两个有一个不重复就可以
-> );
-
自增约束
自动添加主键约束
create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
添加记录：insert into user3 (name) values('张三');
mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
+----+------+
再次添加记录：insert into user3 (name) values('张三');
mysql> select * from user3;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 张三 |
+----+------+
如果创建表时忘记添加主键约束该怎么添加主键？
create table user4(
-> id int,
-> name varchar(20)
-> );
decs user4
mysql> desc user4
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
我们可以 alter table user4 add primary key(id);或者输入alter table user4 modify id int primary key;
mysql> desc user4
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
同理，可以添加主键也可以删除主键：
alter table user4 drop primary key;
-
唯一约束
约束修饰的字段的值不可以重复
create table user5(
-> id int,
-> name varchar(20)
-> );
desc user5
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
输入：alter table user5 add unique(name);
mysql> desc user5
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
添加记录：insert into user values(1,'张三');
mysql> select * from user5;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
+----+------+
再次添加记录：insert into user values(1,'张三');
报错，重复
把名字改成李四就可以（insert into user values(1,'李四');）
也可以在创建的时候直接添加约束
create table user6(
id int,
name varchar(20)
unique(name)
);
输出为
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
此外，unique还可以改变位置
create table user7(
-> id int,
-> name varchar(20),unique
-> );
mysql> desc user7
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> create table user8(
-> id int,
-> name varchar(20),
-> unique(id,name)//用法同联合主键，有一个不重复的就可以
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
- 如何删除唯一约束？
alter table user7 drop index name;
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
- 如何添加唯一约束？
alter table user7 modify name varchar(20) unique;
-
非空约束
修饰的字段不能为空 NULL
mysql> create table user9(
-> id int,
-> name varchar(20) not null
-> );
输出：
mysql> desc user9
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
可以看到 name值的null显示不能为空，即添加记录时name的null值不能为空，即
输入 insert into user9 values(1);会出错
输入insert into user9 values(1，’张三‘)就不会报错
-
默认约束
当插入字段值的时候，没有传值，就会使用默认值
mysql> create table user10(
-> id int,
-> name varchar(20),
-> age int default 10
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into user10 (id,name) values(1,'zhangsan');//不传年龄就默认为10
Query OK, 1 row affected (0.01 sec)
mysql> select * from user10;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 10 |
+------+----------+------+
-
外键约束
涉及两个表：父表（主表）、子表（副表）
例如，有一个班级表（主表）
create table classes(
-> id int primary key,
-> name varchar(20)
-> );
还有一个学生表（副表）
create table students(
-> id int primary key,
-> name varchar(20),
-> class_id int,
-> foreign key(class_id) references classes(id)//指明副表里的id来自哪个表
-> );
分别desc
desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
首先往主表里插入数据：
insert into classes values(1,'一班');
insert into classes values(2,'二班');
insert into classes values(3,'三班');
insert into classes values(4,'四班');
查看数据------------select * from classes;
select * from classes;
+----+------+
| id | name |
+----+------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
+----+------+
之后往副表里插入数据：
insert into students values(1001,'甲'，1);
insert into students values(1002,'乙'，2);
insert into students values(1003,'丙'，3);
insert into students values(1004,'丁'，4);
没有报错，但是 若insert into students values(1005,'小明'，5);就会报错，因为
主表中没有的数据值，在副表中是不可以使用的。
主表中的信息被副表引用，是不可以被删除的
总结自 [code158编程俱乐部]http://www.code158.com mysql编程系列教程