表完整性约束
表完整性约束
作用,用于保证数据的完整性和一致性
| 约束条件 | 说明 |
|---|---|
| primary key | 标识该字段为该表的主键,可以唯一的标识记录,不可以为空 |
| foreing key | 标识该字段为表的外键,实现表与表之间的关联 |
| not null | 标识该字段不能为空 |
| unique key | 标识该字段的值是唯一的,可以为空,一个表可以有多个unique key |
| auto_increment | 标识该字段的值是自动增长(整数类型,而且是主键) |
| default | 为该字段设置默认值 |
| unsigned | 无符号,正数 |
| zerofill | 使用0填充,例如000000001 |
default(默认值)和not null(是否允许空值)
- 默认值不允许为函数
- NULL值就是没有值或缺值
- 允许NULL值的列，就是允许在插入行时不给出该列的值
- 不允许NULL值的列在插入或更新行时，该列必须有值
- 不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。
- 如果指定‘’两个单引号，其间没有字符，这在NOT NULL列是允许的
mysql> use test1;
Database changed
mysql> create table student4(
-> id int not null,
-> name varchar(50) not null,
-> sex enum('m','f') default 'm' not null,
-> age int unsigned default 18 not null,
-> hobby set('disc','book','music') default 'disc,book' not null);
Query OK, 0 rows affected (0.19 sec)
mysql> desc student4;
+-------+----------------------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+-----------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| sex | enum('m','f') | NO | | m | |
| age | int(10) unsigned | NO | | 18 | |
| hobby | set('disc','book','music') | NO | | disc,book | |
+-------+----------------------------+------+-----+-----------+-------+
5 rows in set (0.00 sec)
unique key(唯一)
可以为空,但是不允许重复
mysql> create table department1(
-> dept_in int,
-> dept_name varchar(50) unique,
-> dept_comment varchar(100) );
unique可以在列名的最后指定,也可以在最后指定
mysql> create table department2(
-> id int,
-> name varchar(50),
-> comment varchar(100),
-> unique(name));
AUTO_INCREMENT
AUTO_INCREMENT告诉mysql，本列每增加一行时自动增量，每次执行一个INSERT操作时，MYSQL自动对该列增量（从而才有了AUTO_INCREMENT），给该列赋予下一个可用的值，这样给每个行分配一个唯一的cust_id,从而可以用作主键值
- 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（通常使它成为主键）
- 可以通过SELECT last_insert_id()语句来获取最后一个AUTO_INCREMENT
主键
- 单列做主键
- 多列做主键(复合主键)
primary key字段的值是不允许重复,且不允许为null
mysql> create table student6(
-> id int primary key not null auto_increment,
-> name varchar(50) not null,
-> sex enum('male','female') not null default 'male',
-> age int not null default 18);
Query OK, 0 rows affected (0.18 sec)
插入数据
mysql> insert into student6(name,sex,age) values
-> ('jack','male',17),
-> ('alice','female',23),
-> ('tianyun','male',40);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student6;
+----+---------+--------+-----+
| id | name | sex | age |
+----+---------+--------+-----+
| 1 | jack | male | 17 |
| 2 | alice | female | 23 |
| 3 | tianyun | male | 40 |
主键不能重复
mysql> insert into student6 values(2,'harry','male',17);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
复合主键
场景:单列不能证明唯一性,需要使用多列创建
| 设置主键为: | host_ip+port=primary key) |
|---|---|
| host_ip | 存储主机IP |
| service_name | 服务名 |
| port | 服务对应的端口 |
| allowy(y,n) | 服务是否允许访问 |
mysql> create table service(
-> ip varchar(15) not null,
-> service varchar(16) not null,
-> port int not null,
-> pritocol enum('tcp','udp') default 'tcp',
-> allow enum('y','n') default 'n',
-> primary key(ip,port));
插入数据
mysql> insert into service values('192.168.122.223','http',80,'tcp','y');
Query OK, 1 row affected (0.04 sec)
mysql> insert into service(ip,service,port) values('192.168.122.223','ftp',21);
Query OK, 1 row affected (0.02 sec)
mysql> insert into service values('192.168.122.220','http',80,'tcp','y');
Query OK, 1 row affected (0.03 sec)
mysql> select * from service;
+-----------------+---------+------+----------+-------+
| ip | service | port | pritocol | allow |
+-----------------+---------+------+----------+-------+
| 192.168.122.220 | http | 80 | tcp | y |
| 192.168.122.223 | ftp | 21 | tcp | n |
| 192.168.122.223 | http | 80 | tcp | y |
+-----------------+---------+------+----------+-------+
3 rows in set (0.00 sec)
foreign key(外键)
存储引擎必须是innodb
用子表的外键关联父表的主键,这两个字段的名称,数据类型和约束都需要相同
- foreign key(name) ---- 设置当前表的外键为name
- references employees(name) ----关联employees表的name字段
- on update cascade ---- 同步更新
- on delete cascade ---- 同步删除
设置父表company.employees
mysql> create table employees(
-> name varchar(50) not null,
-> mail varchar(20),
-> primary key(name));
Query OK, 0 rows affected (0.17 sec)
设置字表company.payroll
mysql> create table payroll(
-> id int auto_increment,
-> name varchar(50) not null,
-> payroll float(8,2) not null,
-> primary key(id),
-> foreign key(name) references employees(name) on update cascade on delete cascade);
Query OK, 0 rows affected (0.24 sec)
向父表员工表中插入值
mysql> desc employees;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(50) | NO | PRI | NULL | |
| mail | varchar(20) | YES | | NULL | |
mysql> insert into employees values
-> ('jack','jack@163.com'),('alice',null);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
向子表薪水表中插入数据
mysql> insert into payroll(name,payroll) values('alice',20000);
Query OK, 1 row affected (0.03 sec)
mysql> insert into payroll(name,payroll) values('tianyun',25300);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`payroll`, CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`name`) REFERENCES `employees` (`name`) ON DELETE CASCADE ON UPDATE CASCADE)
发现员工表中没有tianyun,所有在薪水表中无法添加
主表中更改name名称,发现子表中name值也改变了
mysql> update employees set name='alice123' where name='alice';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from payroll;
+----+----------+----------+
| id | name | payroll |
+----+----------+----------+
| 2 | alice123 | 20000.00 |
+----+----------+----------+
1 row in set (0.00 sec)
删除员工表中一位员工,发现新水表中该员工也会自动删除
mysql> delete from employees where name='alice123';
Query OK, 1 row affected (0.01 sec)
mysql> select * from payroll;
Empty set (0.00 sec)
