python数据库表的完整性约束
约束与数据类型宽度一样,是可选参数
作用: 用于保证数据的完整性和一致性
约束种类:
PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型，而且为主键)
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
例如:
创建一个t1表,id字段为int类型,name字段为varchar类型宽度为50,sex字段为单选enum类型,不可为空,默认为man,age字段为int类型,必须为正数,可以为空,默认为20
create table t1(
id int,
name varchar(50),
sex enum('man','female')not null default 'man',
age int unsigned null default 20
);
not null
# id字段默认可以插入空
create table t1(id int);
desc t1;
insert into t1 values();
# 设置id字段不能为空
create table t2(id int not null);
default
# 设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值
create table t3(id int default 123);
alter table t3 modify id int not null default 2;
unique
独一无二,唯一属性:id
是一种key,唯一键
设置唯一约束 unique
方法一:
mysql> create table t1(
id int,
#设置字段的值不能重复
name varchar(20) unique,
age int
);
方法二:
create table t2(
id int,
name varchar(20),
age int,
constraint uk_name unique(需要约束的字段名)
);
联合唯一:同样的地址和同样的端口不能重复
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);
insert into service values
(1,'nginx','1,1,1,1',80),
(2,'apache','2.2.2.2',8080);
PRIMARY KEY
主键为了保证表中的每一条数据的该字段都是表格中的唯一值
主键可以包含一个字段或多个字段.当主键包含多个栏位时,称为组合键(Composite Key),也可以叫联合主键.
主键可以在建置新表格时设定(运用 CREATE TABLE 语句),或是以改变现有的表格架构方式设定(运用 ALTER TABLE).
主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合.
单列做主键
方法一: not null+unique
create table t1(
id int not null unique,
name varchar(20) not null unique,
comment varchar(100)
);
方法二: 在某一个字段后用primary key
create table t2(
id int,
name varchar(20) primary key,
comment varchar(100)
);
方法三: 在所有字段后单独定义primary key
create table t3(id int ,name char(20),primary key(id));
方法四: 给已经建成的表添加主键约束
create table t4(
id int,
name varchar(20),
comment varchar(100));
alter table t4 modify comment varchar(100) primary key;
联合主键
create table t5(
id int,
port int,
primary key(id,port)
);
desc t5
看key,两个都写的是pri,两个联合起来作为主键,他们两个作为一个主键,不能再有其他的主键了,也就是在创建表的时候,只能出现一次primary key方法
多列做主键
create table t6(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,service_name)
);
insert into t6 values
('1.1.1.1','3306','mysql'),
('2.2.2.2','3306','mongodb')
;
auto_increment(必须设置唯一,才能设置自增)
约束字段为自动增长,被约束的字段必须同时被key约束,也就是说只能给约束成key的字段加自增属性,默认起始位置为1,步长也为1
# 不指定id,自动增长
mysql> create table t7(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
mysql> insert into t7(name) values
('bob'),
('jack'),
('jary'),
('pack')
;
mysql> select * from t7;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | bob | male |
| 2 | jack | male |
| 3 | jary | male |
| 4 | pack | male |
+----+------+------+
4 rows in set (0.00 sec)
# 也可以指定id
mysql> insert into t7 values(10,'haha','female');
Query OK, 1 row affected (2.29 sec)
mysql> select * from t7;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | bob | male |
| 2 | jack | male |
| 3 | jary | male |
| 4 | pack | male |
| 10 | haha | female |
+----+------+--------+
5 rows in set (0.00 sec)
# 对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
mysql> delete from t7;
Query OK, 6 rows affected (2.33 sec)
mysql> select * from t7;
Empty set (0.00 sec)
mysql> insert into t7(name) values('ysb');
Query OK, 1 row affected (2.31 sec)
mysql> select * from t7;
+----+------+------+
| id | name | sex |
+----+------+------+
| 12 | ysb | male |
+----+------+------+
1 row in set (0.00 sec)
# 应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它
mysql> truncate t7;
Query OK, 0 rows affected (0.21 sec)
mysql> insert into t7(name) values('xixi');
Query OK, 1 row affected (0.44 sec)
mysql> select * from t7;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | xixi | male |
+----+------+------+
1 row in set (0.00 sec)
foreign key 外键
把两个不同的事物关联在一起,其实就是标明表和表之间的关系
表和表之间关系有三种:
一对一
多对一
多对多
事例:分别创建两个表,让一个表关联另一个表的外键
create table t1_class(id int unique,class_name char(12));
#foreign key 设置外键,references 关联
create table t2_stu(id int,name char(12),class_id int,foreign key(class_id) references t1_class(id));
#插入数据
insert into t1_class values(1,'py19');
insert into t1_class values(2,'py20');
insert into t2_stu values(1,'aaa',1);
insert into t2_stu values(1,'bbb',2);
# 直接删除报错,因为有外键关联
delete from t1_class where id = 2;
#解决方法一: 需要先删除外键关联的数据,然后再删除外键
delete from t2_stu where name = 'bbb';
delete from t1_class where id = 2;
方法二: 定义同步更新和同步删除,update cascade,delete cascade
create table t3_class(id int primary key,class_name char(12));
create table t4_stu(id int primary key,name char(12),class_id int,foreign key(class_id) references t3_class(id) on update cascade on delete cascade);
insert into t3_class values(1,'py19');
insert into t3_class values(2,'py20');
insert into t4_stu values(1,'aaa',1);
insert into t4_stu values(2,'bbb',2);
# 删除t3表中id等于2的数据,关联表t4_stu会自动把关联id=2的所有数据都删除
delete from t3_class where id = 2;
select * from t4_stu;
# 修改t3表中id等于1的数据改为id等于3,关联表t4_stu会自动把关联id=1的数据改为id=3
update t3_class set id = 3 where id = 1;
select * from t3_class;
select * from t4_stu;
总结一下foreign key的下面几个约束作用：
1、先要建立被关联的表才能建立关联表
2、在插入数据记录的时候,要先想被关联表中插入数据,才能往关联表里面插入数据
3、更新或者删除数据的时候,都需要考虑关联表和被关联表的关系
解决方案：
a.删除表的时候,先删除关联表,再删除被关联表
b.重建表的时候,在加外键关联的时候加上这两句: on delete cascade 和 on update cascade
浙公网安备 33010602011771号