MySQL之约束、修改数据表

一、约束

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

列级约束：对一个数据列建立的约束，既可以在列定义时声明，亦可以在列定义后声明

表级约束：对多个数据列建立的约束，只能在列定义后声明

二、FOREGIN KEY(外键约束)

保持数据一致性，完整性；实现一对一或一对多的关系；

外键约束的要求：

1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表；

2.数据表的存储引擎只能为InnoDB；

3.外键列和参照列必须具有相似的数据类型；其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同；

4.外键列和参照列必须创建索引。如果参照列不存在索引的话，MySQL不会自动创建索引。但如果参照列为主键的话，则会自动创建索引。主键在创建的同时会自动创建索引，所以参照列其实已经有了索引。而外键列上没有创建索引，Mysql则会自动创建索引。

三、编辑数据表的默认存储引擎

MySQL配置文件：default-storage-engine=INNODB(windos下在my.ini中，linux下在/etc/my.cnf)

四、外键约束的参照操作

1.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行；

2.SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子列表没有指定NOT NULL

3.RESTRICT:拒绝对父表的删除或更新操作。

4.NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同

五、数据表列处理

1.添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name colume_definition [FIRST| AFTER col_name]

eg:ALTER TABLE user ADD age TINYINT UNSIENED NOT NULL FIRST(添加到所有列的前端)

eg:ALTER TABLE user ADD passwd  VARCHAR(20) NOT NULL  AFTER id（添加到id列的后边）

2.添加多列：

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,......)(不能指定位置，默认添加在后)

3.删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

4.添加主键约束（在表没有主键的时候）

ALTER TABLE tbl_name ADD [CONSATRAINT []symbol]] PRIMARY KEY [index_type] (index_col_name,..)

eg：ALTER TABLE users ADD PYIMARY KEY (id);

5.删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY

6.添加唯一约束

ALTER TABLE tbl_name ADD [CONSATRAINT []symbol]] UNIQUE [INDEX|KEY]  [index_name]  [index_type] (index_col_name,..)

可以添加多个而主键只能添加一个

eg:ALTER TABLE users ADD UNIQUE KEY (username);

7.删除唯一约束

ALTER TABLE tbl_name  DROP  {INDEX|KEY}  index_name

index_name 通过命令：SHOW INDEXES FROM  users\G;查找

8.添加外键约束

ALTER TABLE tbl_name ADD [CONSATRAINT []symbol]] FOREIGN KEY  [index_name]  (index_col_name,..) reference_definition

eg:ALTER TABLE users ADD FOREIGN KEY  (pid)  REFERENCES provinces (id);

9.删除外键约束

 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

fk_symbol：通过命令：SHOW CREATE TABLE users（数据表名字）;来查找，由系统给的

10.添加/删除默认约束

ALTER  TABLE  tbl_name  ALTER  [COLUMN]  col_name {SET DEFAULT iteral | DROP DEFAULT}

eg:ALTER TABLE users ALTER age SET 15;

五、修改数据表

1.修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition  [FIRST|after col _name]

修改类型的时候，如果大类型修改为小类型，有可能会造成数据丢失

eg:ALTER TABLE users MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

2.修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column _definition [FIRST|AFTER col_name]

3.数据表更名

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] ...