完整性练习
**下面所要求的约束可以创建表的同时建立,也可以先建表再添加约束。
- 建立图书表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(bname,price)
-> values(‘红楼梦’,20);
ERROR 1364 (HY000): Field ‘ISBN’ doesn’t have a default value - 建立读者表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) - 建立借阅表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) - 请验证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
, CONSTRAINTfk1_borrow
FOREIGN KEY (rid
) REFERENCESr eader
(rid
)) - 查看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