Python-4.40 数据库约束条件

• 约束条件not null与default

mysql> create table t16(
    -> id int,
    -> name char(6),
    -> sex enum('male','female') not null default 'male'
    -> );
Query OK, 0 rows affected (0.35 sec)


mysql> insert into t16(id,name) values(1,'vivian');
Query OK, 1 row affected (0.03 sec)


mysql> select * from t16;
+------+--------+------+
| id   | name   | sex  |
+------+--------+------+
|    1 | vivian | male |
+------+--------+------+
1 row in set (0.00 sec)

• 约束条件unique key

单列唯一

	#方式一
	create table department(
		id int unique,
		name char(10) unique
	);
	#方式二：
	create table department1(
		id int,
		name char(10)，
		unique(id),
		unique(name)
	);

联合唯一

create table services(
	id int,
	ip char(15),
	port int,
	unique(id),
	unique(ip,port)
);

mysql> create table services(
    -> id int,
    -> ip char(15),
    -> port int,
    -> unique(id),
    -> unique(ip,port)
    -> );
Query OK, 0 rows affected (0.28 sec)


mysql> insert into services values
    -> (1,'192.168.11.10',80),
    -> (2,'192.168.11.10',81),
    -> (3,'192.168.11.13',80);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from services;
+------+---------------+------+
| id   | ip            | port |
+------+---------------+------+
|    1 | 192.168.11.10 |   80 |
|    2 | 192.168.11.10 |   81 |
|    3 | 192.168.11.13 |   80 |
+------+---------------+------+
3 rows in set (0.00 sec)


mysql> insert into services values
    -> (4,'192.168.11.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.11.10-80' for key 'ip'

• 约束条件primary key
  约束：not null unique
  存储引擎（innodb）:对于innodb存储引擎来说，一张表内必须有一个主键

1. 单列主键

mysql> create table t17(
    -> id int primary key,
    -> name char(16)
    -> );
Query OK, 0 rows affected (0.23 sec)


mysql> insert into t17 values
    -> (1,'winnie'),
    -> (2,'allen');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> insert into t17 values
    -> (2,'wxx');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'


mysql> insert into t17(name) values
    -> ('wxx');
ERROR 1364 (HY000): Field 'id' doesn't have a default value


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

create table t18(
	id int not null unique,
	name char(16)
);

2. 复合主键

mysql> create table t19(
    -> ip char(15),
    -> port int,
    -> primary key(ip,port)
    -> );
Query OK, 0 rows affected (0.25 sec)


mysql> insert into t19 values
    -> ('1.1.1.1',80),
    -> ('1.1.1.1',81);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t19;
+---------+------+
| ip      | port |
+---------+------+
| 1.1.1.1 |   80 |
| 1.1.1.1 |   81 |
+---------+------+

• 约束条件auto_increment

mysql> create table t20(
    -> id int primary key auto_increment,
    -> name char(16)
    -> );
Query OK, 0 rows affected (0.23 sec)


mysql> insert into t20(name) values
    -> ('winnie'),
    -> ('allen'),
    -> ('vivian');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> insert into t20(id,name) values
    -> (7,'liuliu');
Query OK, 1 row affected (0.04 sec)


mysql> insert into t20(name) values
    -> ('allen1'),
    -> ('allen2'),
    -> ('allen3');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from t20;
+----+--------+
| id | name   |
+----+--------+
|  1 | winnie |
|  2 | allen  |
|  3 | vivian |
|  7 | liuliu |
|  8 | allen1 |
|  9 | allen2 |
| 10 | allen3 |
+----+--------+
7 rows in set (0.00 sec)

• 约束条件之foreign key
  建立表之间的关系

#1、建立表关系：
	#先建被关联的表,并且保证被关联的字段唯一
	create table dep(
		id int primary key,
		name char(16),
		comment char(50)
	);


	#再建立关联的表
	create table emp(
		id int primary key,
		name char(10),
		sex enum('male','female'),
		dep_id int,
		foreign key(dep_id) references dep(id) 
		on delete cascade 
		on update cascade
	);

#2、插入数据
#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");

#再往关联表插入记录
insert into emp values
(1,'allen','male',1);

insert into emp values
(2,'winnie','male',1),
(3,'lff','female',2),
(4,'vivian','male',3),
(5,'zyp','male',2);


delete from emp where dep_id=1;
delete from dep where id=1;


delete from dep where id=3;

mysql> create table dep(
    -> id int primary key,
    -> name char(16),
    -> comment char(50)
    -> );
Query OK, 0 rows affected (0.23 sec)


mysql> create table emp(
    -> id int primary key,
    -> name char(10),
    -> sex enum('male','female'),
    -> dep_id int,
    -> foreign key(dep_id) references dep(id)
    -> on delete cascade
    -> on update cascade
    -> );
Query OK, 0 rows affected (0.34 sec)


mysql> insert into dep values
    -> (1,"IT","技术能力有限部门"),
    -> (2,"销售","销售能力不足部门"),
    -> (3,"财务","花钱特别多部门");
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> insert into emp values
    -> (1,'allen','male',1);
Query OK, 1 row affected (0.03 sec)
mysql> insert into emp values
    -> (2,'winnie','male',1),
    -> (3,'lff','female',2),
    -> (4,'vivian','male',3),
    -> (5,'zyp','male',2);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from dep;
+----+------+------------------+
| id | name | comment          |
+----+------+------------------+
|  1 | IT   | 技术能力有限部门 |
|  2 | 销售 | 销售能力不足部门 |
|  3 | 财务 | 花钱特别多部门   |
+----+------+------------------+
3 rows in set (0.00 sec)


mysql> select * from emp;
+----+--------+--------+--------+
| id | name   | sex    | dep_id |
+----+--------+--------+--------+
|  1 | allen  | male   |      1 |
|  2 | winnie | male   |      1 |
|  3 | lff    | female |      2 |
|  4 | vivian | male   |      3 |
|  5 | zyp    | male   |      2 |
+----+--------+--------+--------+
5 rows in set (0.00 sec)

mysql> delete from emp where dep_id=1;
Query OK, 2 rows affected (0.04 sec)


mysql> select * from emp;
+----+--------+--------+--------+
| id | name   | sex    | dep_id |
+----+--------+--------+--------+
|  3 | lff    | female |      2 |
|  4 | vivian | male   |      3 |
|  5 | zyp    | male   |      2 |
+----+--------+--------+--------+
3 rows in set (0.00 sec)


mysql> delete from dep where id=3;
Query OK, 1 row affected (0.03 sec)


mysql> select * from dep;
+----+------+------------------+
| id | name | comment          |
+----+------+------------------+
|  1 | IT   | 技术能力有限部门 |
|  2 | 销售 | 销售能力不足部门 |
+----+------+------------------+
2 rows in set (0.00 sec)


mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex    | dep_id |
+----+------+--------+--------+
|  3 | lff  | female |      2 |
|  5 | zyp  | male   |      2 |
+----+------+--------+--------+
2 rows in set (0.00 sec)