保护数据库
完整性
- 定义
- 分类:实体完整性、域完整性、用户定义完整性
- 创建约束(主键、外键)
(1) 创建表的同时创建约束
1) 主键primary key
2) 外键foreign key
父关键字在自己表中必须是主键;
父子必须完全一样
3) 唯一unique
4) 默认值default
举例
create table student
(sno char(4) primary key,
sname char(20) not null,
sage int,
ssex char(2) default ‘男’);
create table sc
(sno char(4),
cno char(3),
grade int,
constraint pk_sc primary key(sno,cno),
constraint fk1_sc foreign key(sno) references student(sno));
(2) 表已经存在,添加约束
1) 主键
alter table student
add primary key(sno);
alter table sc
add primary key(sno,cno);
2) 外键
alter table sc
add constraint fk1_sc foreign key(sno) references student(sno);
3) 唯一
alter table student
add constraint uni unique(sname);
4. 查看约束
show create table student;
5. 删除约束
1)主键
alter table student
drop primary key;
2)外键
alter table sc
drop foreign key fk1_sc;
3)唯一
alter table student
drop index uni(键名);
练习:
- 建立图书表books(ISBN char(14),bname char(30),price int);其中ISBN为主键,price默认为30,bname必须唯一;
mysql> create table books
-> (ISBN char(14) primary key,
-> bname char(30) unique,
-> price int default 30);
Query OK, 0 rows affected (0.08 sec) - 请验证books表主键的作用;
不能为空:
mysql> insert into books
-> values();
ERROR 1364 (HY000): Field ‘ISBN’ doesn’t have a default value
唯一:
mysql> insert into books(ISBN)
-> values(“111”);
Query OK, 1 row affected (0.03 sec)
mysql> insert into books(ISBN)
-> values(“111”);
ERROR 1062 (23000): Duplicate entry ‘111’ for key ‘PRIMARY’ - 建立读者表reader(rid char(4),rname char(20),rage int);其中rid为主键。
mysql> create table reader
-> (rid char(4) primary key,
-> rname char(20),
-> rage int);
Query OK, 0 rows affected (0.14 sec) - 建立借阅表borrow(rid char(4), ISBN char(14),amount int);其中rid和ISBN号为联合主键,rid上有外键,参考reader表的rid列了,ISBN上有外键,参考books表的ISBN列;
mysql> create table borrow
-> (rid char(4), ISBN char(14),amount int,
-> constraint pk1sc primary key(rid,ISBN),
-> constraint fk1sc foreign key(ISBN) references books(ISBN));
Query OK, 0 rows affected (0.06 sec) - 请验证borrow表rid上外键的作用;
mysql> select * from reader;
Empty set (0.00 sec)
mysql> insert into borrow
-> values();
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (xkdb
.borrow
, CONSTRAINT fk1sc
FOREIGN KEY (ISBN
) REFERENCES books
(ISBN
))
6. 查看borrow表上有哪些约束,约束名称是什么。
mysql> show create table borrow;
- 删除borrow上的主键约束;
mysql> alter table borrow
-> drop primary key;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0 - 删除bname上的唯一约束;
mysql> alter table books
-> drop index bname;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0 - 删除borrow的rid上的外键约束;
mysql> alter table borrow
-> drop foreign key fk1sc ;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0