MySQL(3)约束以及修改数据表

MySQL外键约束的要求解析

约束
1.保证数据的完整性和一致性。
2.分为表级约束和列级约束
3.约束类型包括：
NOT NULL（非空约束）
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）
FOREIGN KEY（外键约束）

FOREIGN KEY（外键约束）
保证数据的完整性和一致性。
实现一对一或一对多关系。

外键约束的要求
1.父表和子表须使用相同的存储引擎，且禁止使用临时表
分析：子表，指具有外键列的表；
子表参照的表成为父表
2.数据表的存储引擎只能InnoDB
3.外键列（FOREIGN）和参照列必须具有相似的数据类型。其中数字长度或是否有符号为必须相同；而字符的长度则可以不同。
4.外键列（FOREIGN）和参照列必须创建索引，若外键列不存在索引的话，MySQL将自动创建索引。

编辑数据表的默认存储引擎
MySQL配置文件
default-storage-engine=INNODB

举例：创建省份表（参照列id）
CREATE TABLE shengfen(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pname VARCHAR(20) NOT NULL);
SHOW CREATE TABLE shengfen;
分析：查看创建命令，确认是InnoDB引擎

下创建子表（外键列pid）
CREATE TABLE users(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES shengfen (id));
分析：外键列（FOREIGN）和参照列必须具有相似的数据类型才能创建成功
外键列（FOREIGN）和参照列必须创建索引，该例子参照列有创建索引（FOREIGN KEY 会自动创建），下可以测试：SHOW INDEXES FROM shengfen\G;
SHOW INDEXES FROM users\G;

MySQL外键约束的参照操作
1.CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2.SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。若使用该选项，必须保证子表列没有指定NOT NULL。
3.RESTIRICT：拒绝对父表的删除或更新操作
4.NO ACTION：标准的SQL关键字，在MySQL中与RESTRICT相同。
举例：CREATE TABLE users1(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES shengfen (id) ON DELETE CASCADE);
分析：父表删操作时加了个CASCADE操作。即父表删操作时也删子表响应的行
下进行测试：
先在父表插入记录：
INSERT shengfen(pname) VALUES(‘A’);
INSERT shengfen(pname) VALUES(‘B’);
INSERT shengfen(pname) VALUES(‘C’);
SELECT * FROM shengfen;
INSERT users1(username,pid) VALUES(‘TOM’,3);// 插入成功
INSERT users1(username,pid) VALUES(‘John’,7);// 插入失败，改为(‘John’,1)
INSERT users1(username,pid) VALUES(‘Rose’,3);// 插入成功
SELECT * FROM users1;
DELETE FROM shengfen WHERE id=3；
SELECT * FROM shengfen;
SELECT * FROM user1;

以上约束只支持InnoDB引擎

MySQL表级约束与列级约束
对一个数据列建立的约束，成为列级约束。
对多个数据列建立的约束，成为表级约束。
列级约束即可在列定义时声明，也可在列定义后声明。表级约束只能在列定义后声明。

修改数据表-添加/删除列
添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name col_defintion [FIRST|AFTER col_name]
分析：FIRST|AFTER插在列前后
举例：
SHOW COLUMNS FROM users1;
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;
SHOW COLUMNS FROM users1;

添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name col_defintion,…..)
只能插到最后

删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name;
举例：
ALTER TABLE users1 DROP truename;
SHOW COLUMNS FROM users1;
删除多列
ALTER TABLE users1 DROP password, DROP age;
SHOW COLUMNS FROM users1;

MySQL修改数据表–添加约束
添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)
举例：
CREATE TABLE users2(username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED);
SHOW CREATE TABLE users2;
ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
SHOW COLUMNS FROM users2;
ALTER TABLE users2 ADD CONSTRAINT PK_users_id PRIMARY KEY (id);
SHOW COLUMNS FROM users2;
分析：CONSTRAINT PK_users_id可以不写

添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_type] (index_col_name,…)
举例：
ALTER TABLE users2 ADD UNIQUE KEY (username);
SHOW CREATE TABLE users2;

添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,…) reference_definition
举例：
SHOW COLUMNS FROM shengfen;
ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES shengfen (id);

添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}
举例：
ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
SHOW COLUMNS FROM users2;
ALTER TABLE users2 ALTER age SET DEFAULT 15;
SHOW COLUMNS FROM users2;

MySQL修改数据表–删除约束
删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
举例：
ALTER TABLE users2 DROP PRIMARY KEY ;
SHOW COLUMNS FROM users2;

删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
举例：
SHOW INDEXES FROM users2\G;
ALTER TABLE users2 DROP INDEX username;
//删除的是约束而不是字段
SHOW COLUMNS FROM users2;
SHOW INDEXES FROM users2\G;

删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
举例：
SHOW CREATE TABLE users2;//找到约束名称
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
SHOW CREATE TABLE users2;
//发现外键已删除，还存在索引。欲删除索引,
ALTER TABLE users2 DROP INDEX pid;

MySQL 修改数据表–修改列定义和更名数据表
修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
举例：
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
SHOW COLUMNS FROM users2;
ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
SHOW COLUMNS FROM users2;
//大类型改到小类型，数据可能丢失

修改列名称
ALTER TABLE tbl_name CHANGE[COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
//可改定义，也可该名称
举例：
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
SHOW COLUMNS FROM users2;

数据表更名
方法1：
ALTER TABLE tbl_name RENAME[TO/AS] new_tbl_name
方法2：
RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2]
举例：
ALTER TABLE users2 RENAME users3;
SHOW TABLES;

法2：RENAME TABLE users3 TO users2;
SHOW TABLES;