实验6.3　用户定义的完整性

【实验目的】

①掌握使用SQL创建CHECK约束。

②掌握使用SQL创建包含数据完整性控制的表。

③掌握使用SQL为表删除约束。

【实验内容】

①使用SQL为sales数据库中的orders表创建CHECK约束。

②使用SQL创建两个新表ord_1和ord_2，表结构与orders相同，包含主键、DEFAULT约束和CHECK约束。

③使用图形界面工具为ord_1表删除主键、DEFAULT约束。

④使用SQL为ord_2表删除主键、DEFAULT约束和CHECK约束。

【实验步骤】

（1）使用SQL创建CHECK约束

为sales数据库中的orders表创建CHECK约束。要求qty的取值大于等于0，dollars的取值大于等于0。

①在图标菜单中单击第一个图标，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

ALTER TABLE orders
ADD CONSTRAINT qty_check CHECK( qty >= 0 ) ENFORCED;
ALTER TABLE orders
ADD CONSTRAINT dollars_check CHECK ( dollars >= 0 ) ENFORCED;

③单击工具栏中的图标或者按下快捷键“Ctrl+Shift+Enter”，执行上面的SQL语句，结果如图6.12所示。

图6.12　成功创建CHECK约束

④验证CHECK约束，在查询窗口输入下面的SQL语句：

UPDATE orders SET qty=-1 WHERE ordno='1011';
UPDATE orders SET dollars=-1 WHERE ordno='1011';

⑤单击工具栏中的图标或者按下快捷键“Ctrl+Shift+Enter”，执行上面的SQL语句，两条语句均未执行成功，提示违背CHECK约束，如图6.13所示。

图6.13　验证CHECK约束

（2）使用SQL创建两个新表

创建两个新表ord_1和ord_2，表结构与orders表相同，包含主键、外键约束、DEFAULT约束和CHECK约束。

①在图标菜单中单击第一个图标，新建一个查询窗口。

②在查询窗口输入下面的SQL语句：

CREATE TABLE ord_1 LIKE orders;
CREATE TABLE ord_2 LIKE orders;

③单击工具栏中的图标或者按下快捷键“Ctrl+Shift+Enter”，执行上面的SQL语句，成功之后，在左边导航栏中刷新后再查看新建的表。

④查看orders和新复制的两个表的约束。

新建窗口输入下面的SQL语句：

SELECT * FROM information_schema.table_constraints
WHERE table_name='ord_1' or table_name='ord_2' or table_name='orders' ;

⑤单击工具栏中的图标或者按下快捷键“Ctrl+Enter”，执行上面的SQL语句，查询结果如图6.14所示。比较三表的约束，可看出新复制的表除了表结构相同，主键约束和CHECK约束已经复制，但CHECK约束名按照表名重新更名，外键约束未复制。(https://www.daowen.com)

图6.14　查看两表的约束

⑥为ord_1、ord_2 表增加外键约束。

新建窗口输入下面的SQL语句：

ALTER TABLE ord_1
ADD CONSTRAINT cid1 FOREIGN KEY (cid) REFERENCES customers (cid),
ADD CONSTRAINT pid1 FOREIGN KEY (pid) REFERENCES products (pid);
ALTER TABLE ord_2
ADD CONSTRAINT cid2 FOREIGN KEY (cid) REFERENCES customers (cid),
ADD CONSTRAINT pid2 FOREIGN KEY (pid) REFERENCES products (pid);

⑦成功执行窗口中的SQL语句后，表明分别为ord_1、ord_2 表创建了两个外键约束。

（3）使用图形界面工具删除主键、外键及约束

为ord_1表删除主键、外键、DEFAULT约束。

①在“Navigator”导航栏的“Schemas”选项页中依次展开节点“sales”→“Tables”，右击“ord_1”，在快捷菜单中选择 “Alter Table...”选项，在查询窗口中显示表的结构。

②删除主键。选择“Columns”界面，选择“ordno”行，单击工具栏中的“PK”对应按钮，或者取消勾选右下方的“Primary Key”，单击“Apply”按钮，即ordno列最左侧的钥匙消失。

③删除外键。选择“Foreign Keys”界面，显示“外键关系”对话框，如图6.15所示。右击对应的键，在出现的菜单中选择“Delete selected”删除外键，单击“Apply”按钮，在出现的对话框中再次单击“Apply”按钮，即可完成外键约束的删除操作。

图6.15　“外键关系”对话框

④删除DEFAULT约束和CHECK约束。选择“Columns”界面，选中想要删除行的“Default/Expression”栏，将其中的值去掉，或者删除“Default”文本框中的值，单击“Apply”按钮即可进行应用设置，如图6.16所示。MySQL WorkBench不支持图形化界面删除CHECK约束，因此参照下一个实验，使用SQL语句删除CHECK约束。