mySQL 建表约束

-- mysql建表约束.sql


-- 1. 主键约束

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

create table user(
	id int primary key,
	name varchar(20)
);

insert into user values(1, '张三');
insert into user values(2, '张三');
insert into user values(null, '张三'); --会失败，不可为空。


-- 联合主键：
--- 联合的主键值加起来不重复即可。
create table user2(
    id int,
    name varchar(20),
    password varchar(20),
    primary key(id,name)
);
describe user2;--输出如下：
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

insert into user2 values(1, '张三','123');
insert into user2 values(2, '张三','123');
insert into user2 values(1, '三','123');

select * from user2;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | 三     | 123      |
|  1 | 张三   | 123      |
|  2 | 张三   | 123      |
+----+--------+----------+



-- 2. 自增约束

与主键约束组合，管控主键的值，自动增加序号，方便使用。
create table user3(
    id int primary key auto_increment,
    name varchar(20)
);

mysql> insert into user3(name) values('zhansna');
mysql> insert into user3(name) values('zhansna');
mysql> insert into user3(name) values('zhansna');

mysql> select * from user3;
+----+---------+
| id | name    |
+----+---------+
|  1 | zhansna |
|  2 | zhansna |
|  3 | zhansna |
+----+---------+


-- 创建表的时候，忘记创建主键约束。
create table user4(
    id int,
    name varchar(20)
);

-- 修改表结构，添加主键。
alter table user4 add primary key(id);
describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


-- 使用modify修改字端，添加约束。
alter table user4 modify id int primary key;
describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 删除主键； 

alter table user4 drop primary key;



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

create table user5(
    id int,
    name varchar(20)
);

alter table user5 add unique(name);
describe user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

--或者
create table user5(
    id int,
    name varchar(20),
    unique(name)
);
--或者
create table user5(
    id int,
    name varchar(20) unique
);

-- 删除唯一约束drop
alter table user5 drop index name;

-- 添加唯一约束modify
alter table user5 modify name varchar(20) unique;


-- 总结
---(1. 建表的时候添加约束
---(2 后期使用alter ...add...
---(3 后期使用alter....modify...

---(4 删除约束alter....drop.....



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

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

insert into user9 values(1, 'zhangsan');
insert into user9(name) values('lisi');

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

select * from user9;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
| NULL | lisi     |
+------+----------+



-- 5. 默认约束
--- 插入字段时，如果没有传值，就会使用默认值。

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

insert into user10(id, name) values(1, 'zhangsan');
--有输入，则代替默认值
insert into user10 values(1, 'zhangsan', 11);

 select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    1 | zhangsan |   11 |
+------+----------+------+



-- 6. 外键约束
--- 涉及到两个表，主表，副表。

--班级：主表(删除表：DROP TABLE table_name;)
create table classes(
    id int primary key,
    name varchar(20)
);

 describe classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


--学生：副表
create table students(
    id int primary key,
	name varchar(20),
    class_id int,
	foreign key(class_id) references classes(id)
);
describe students;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

---插入数据到classes中
insert into classes values(1,'一般');
insert into classes values(2,'二般');
insert into classes values(3,'三般');
insert into classes values(4,'四般');
select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一般   |
|  2 | 二般   |
|  3 | 三般   |
|  4 | 四般   |
+----+--------+

---插入数据到students中
insert into students values(1001,'章三', 1);
insert into students values(1002,'里斯', 2);
insert into students values(1003,'王武', 3);
insert into students values(1004,'找刘', 4);
select * from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 章三   |        1 |
| 1002 | 里斯   |        2 |
| 1003 | 王武   |        3 |
| 1004 | 找刘   |        4 |
+------+--------+----------+
insert into students values(1005,'拿吧',5);
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`pet`.`students`, CONSTRAINT 
	`students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes`
	 (`id`))

--- 1. 要是主表中没有的数据值，副表中不可以使用。

delete from classes where id=4;
ERROR 1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails (`pet`.`students`, CONSTRAINT 
	`students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` 
	`id`))
--- 2. 主表记录被副表使用，则不可以删除。