数据库完整性练习

完整性练习
**下面所要求的约束可以创建表的同时建立，也可以先建表再添加约束。

1. 建立图书表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)
2. 请验证books表主键的作用；
   mysql> insert into books(bname,price)
   -> values(‘红楼梦’,20);
   ERROR 1364 (HY000): Field ‘ISBN’ doesn’t have a default value
3. 建立读者表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.08 sec)
4. 建立借阅表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 pk_borrow primary key(rid,ISBN),
   -> constraint fk1_borrow foreign key(rid) references reader(rid),
   -> constraint fk2_borrow foreign key(ISBN) references books(ISBN));
   Query OK, 0 rows affected (0.09 sec)
5. 请验证borrow表rid上外键的作用；
   mysql> insert into borrow
   -> values(‘0103’,‘mmo’,20);
   ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
   ails (xkdb.borrow, CONSTRAINT fk1_borrow FOREIGN KEY (rid) REFERENCES r eader (rid))
6. 查看borrow表上有哪些约束，约束名称是什么。
   mysql> show create table borrow;
   ±-------±---------------------------------------------------------------------

---

---

---

------------------------------------------------------------------------+
| Table | Create Table

                                                                    |

±-------±---------------------------------------------------------------------

---

---

------------------------------------------------------------------------+
| borrow | CREATE TABLE borrow (
rid char(4) NOT NULL DEFAULT ‘’,
ISBN char(14) NOT NULL DEFAULT ‘’,
amount int(11) DEFAULT NULL,
PRIMARY KEY (rid,ISBN),
KEY fk2_borrow (ISBN),
CONSTRAINT fk1_borrow FOREIGN KEY (rid) REFERENCES reader (rid),
CONSTRAINT fk2_borrow FOREIGN KEY (ISBN) REFERENCES books (ISBN)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
±-------±---------------------------------------------------------------------

---

---

------------------------------------------------------------------------+
1 row in set (0.00 sec)
7. 删除borrow上的主键约束；
mysql> alter table borrow
-> drop primary key;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
8. 删除bname上的唯一约束；
mysql> alter table books
-> drop index bname;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
9. 删除borrow的rid上的外键约束；
mysql> alter table borrow
-> drop foreign key fk1_borrow;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table borrow
-> drop foreign key fk2_borrow;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0