第十三章 索引和约束

1. 基本描述

　　本章主要讲解索引和约束。

2. 基本样例

SELECT dept_id, name FROM department
WHERE name LIKE 'A%';

ALTER TABLE department ADD INDEX dept_name_idx (name);

SHOW INDEX FROM department \G

ALTER TABLE department DROP INDEX dept_name_idx;

ALTER TABLE department ADD UNIQUE dept_name_idx (name);

INSERT INTO department (dept_id, name) VALUES (999, 'Operations');

ALTER TABLE employee ADD INDEX emp_names_idx (lname, fname);

SELECT emp_id, fname, lname FROM employee WHERE emp_id IN (1, 3, 9, 15);

SELECT cust_id, SUM(avail_balance) tot_bal FROM account
WHERE cust_id IN (1, 5, 9, 11)
GROUP BY cust_id;

EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal FROM account
WHERE cust_id IN (1, 5, 9, 11)
GROUP BY cust_id \G

ALTER TABLE account ADD INDEX acc_bal_idx (cust_id, avail_balance);

EXPLAIN SELECT cust_id, SUM(avail_balance) tot_bal
FROM account WHERE cust_id IN (1, 5, 9, 11) GROUP BY cust_id \G

SELECT product_type_cd, name FROM product_type;

SELECT product_type_cd, product_cd, name FROM product ORDER BY product_type_cd;

###
UPDATE product SET product_type_cd = 'XYZ'WHERE product_type_cd = 'LOAN';

UPDATE product_type SET product_type_cd = 'XYZ' WHERE product_type_cd = 'LOAN';
###

ALTER TABLE product DROP FOREIGN KEY fk_product_type_cd;

ALTER TABLE product ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd) REFERENCES
product_type (product_type_cd) ON UPDATE CASCADE;

UPDATE product_type SET product_type_cd = 'XYZ' WHERE product_type_cd = 'LOAN';

SELECT product_type_cd, name FROM product_type;

SELECT product_type_cd, product_cd, name FROM product ORDER BY product_type_cd;