约束以及修改数据表

约束

1. 约束保证数据的完整性和一致性。

2. 约束分为表级约束和列级约束。

3. 约束类型包括：

1. NOT NULL
2. PRIMARY KEY
3. UNIQUE KEY
4. DEFAULT
5. FOREIGN KEY 

外键约束(FORIGN KEY)

• 保持数据一致性，完整性
• 实现一对一或一对多关系
• 要求
  1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
  2. 数据表的存储引擎只能为InnoDB。
  3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
  4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL会自动创建索引。
  • 参照列没有索引，MySQL会自动创建索引，因为参照列含有主键，创建主键时MySQL会默认创建所用；
  • 外键列的索引也是MySQL根据参照列自动创建的
• 用例

CREATE TABLE provinces(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY key,
pname VARCHAR(20) NOT NULL
);
SHOW CREATE TABLE provinces;    #查看数据表的存储引擎是否为InnoDB

CREATE TABLE users(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);
SHOW INDEX FROM provinces;    #查看参照列是否有索引

SHOW INDEX FROM provinces\G;    #换成按列打印显示，每个字段打印到单独的行
SHOW INDEX FROM users;    #查看外键列是否有索引

SHOW INDEX FROM users\G;    #查看外键列是否有索引
SHOW CREATE TABLE users;    #查看数据表users的创建历史命令

外键约束的参照操作

1. CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2. SET NULL:从父表删除或更新行并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定的NOT NULL
3. RESTRICT:拒绝对父表的删除或更新操作。
4. NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同

(root@localhost) [TEST]> CREATE TABLE users1(
    -> id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
    -> );

表级约束与列级约束

• 对一个数据列建立的约束，称为列级约束
• 对多个数据列建立的约束，称为表级约束
• 列级约束既可以在列定义时声明，也可以在列定义后声明。表级约束只能在列定义后声明

修改数据表

• 添加单列

ALTER TABLE tb1_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
例子：
ALTER TABLE USERS1 ADD age TINYINT UNSIGNED NOT NULL FIRST;
ALTER TABLE USERS1 ADD salary FLOAT(8,2) UNSIGNED NOT NULL AFTER pid;

• 添加多列

添加多列只能在最后一列后面添加
ALTER TABLE tb1_name ADD [COLUMN](col_name column_definition,...)

• 删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name
ALTER TABLE users1 DROP age，ADD age TINYINT UNSIGNED NOT NULL FIRST;

• 添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type](index_col_name)
[CONSTRAINT [symbol]]相当于给约束起名字
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

• 删除主键约束

数据表有且只有一个主键所以不需要指定列名称
ALTER TABLE tbl_name DROP PRIMARY KEY
ALTER TABLE users2 DROP PRIMARY KEY

• 添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name,...)
ALTER TABLE users2 ADD UNIQUE (username);

• 删除唯一约束

唯一约束有多个，所以需要指定列
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
ALTER TABLE users2 DROP INDEX username;

• 添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name](index_col_name,...)reference_definition
ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces(id);

• 删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
SHOW CREATE TABLE users2;
注意这几行
KEY `pid` (`pid`),
CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES provinces` (`id`)
可以看到这个外键的名称是users2_ibfk_1，下面就可以进行删除了
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
删除索引
ALTER TABLE users2 DROP INDEX pid;

• 添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
ALTER TABLE users2 ALTER age SET DEFAULT 15;
ALTER TABLE users2 ALTER age DROP DEFAULT;

• 修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name colmn_definition [FIRST|AFTER col_name]
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

• 修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name colmn_definition [FIRST|AFTER col_name]
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

• 数据表更名

方法一：

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
ALTER TABLE users2 RENAME TO user2;

方法二：

RENAME TABLE tbl_name TO new_tbl_name[,tbl_name2 TO new_tbl_name2]...
RENAME TABLE users2 TO user2;