MySQL约束及相关操作

MySQL约束分类

按功能划分：

主键约束：PRIMARY KEY非空约束：NOT NULL唯一约束：UNIQUE KEY默认约束：DEFAULT外键约束：FOREIGN KEY

按数据列的数目划分：

对一个数据列建立的约束，称为列级约束对多个数据列建立的约束，称为表级约束列级约束既可以在列定义时声明，也可以定义后声明；表级约束只能在列定义后声明一般使用列级约束NOT NULL和DEFAULT只存在列级约束

相关操作

添加单列

语法：    ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];示例：    ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; // 添加到最后面    ALTER TABLE users1 ADD password VARCHAR(20) NOT NULL AFTER username; //添加到username后    ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST; //添加到最前面    SHOW COLUMNS FROM user1;

添加多列

语法：    ALTER TABLE tbl_name ADD [COLUMN](col_name, col_definition,...)    不能指定位置，直接添加到所有字段最后。

删除列

语法：    ALTER TABLE tbl_name DROP [COLUMN] col_name;示例：    ALTER TABLE users1 DROP truename;    ALTER TABLE users1 DROP password, DROP age, ADD ...; // 操作用逗号分开

添加主键

语法：    ALTER TABLE tbl_name ADD [CONSTRAINT [symbol］ PRIMARY KEY [index_type](index_col_name,...)示例：    CREATE TABLE users2(    ->username VARCHAR(20) NOT NULL,    ->pid SMALLINT UNSIGNED    ->);    ALTER TBALE users2 ADD id SMALLINT UNSIGNED;//添加列    ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id); //添加约束并制定约束名为PK_users2_id

添加唯一约束

语法：    ALTER TABLE tbl_name ADD [CONSTRAINT[symbol］ UNIQUE [INDEX|KEY] [index_type] (index_col_name,...)    唯一约束可以有多个示例：    ALTER TABLE users2 ADD UNIQUE (username);

添加外键约束

语法：    dex_col_name,...) reference_definition示例：    ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces(id);

添加删除默认约束

语法：    ALTER TABLE tbl_name **ALTER** [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}示例：    ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;    ALTER TABLE users2 **ALTER** age SET DEFAULT 15;    ALTER TABLE users2 **ALTER** age DROP DEFAULT;

删除主键约束

ALTER TABLE users2 DROP PRIMARY KEY; // 执行后id的主键约束没有，username的唯一约束变为主键约束

删除唯一约束

语法：    ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;示例：    SHOW INDEXES FROM users2\G;//查看唯一约束名字    ALTER TABLE users2 DROP INDEX username;

删除外键约束

语法：    ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;示例：    SHOW CREATE TABLE users2;    ALTER TABLE users2 DROP FOREIGN KEY ....;    ALTER TABLE users2 DROP INDEX pid; //删除pid上的索引

修改列定义

语法：    ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]示例：    SHOW COLUMNS FROM users2;    ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; //id移动到顶端。    **注意：由大类型改成小类型可能会造成数据丢失**

数据表更名

单表操作：ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name;多表操作：RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO new_tbl_name2,...;