MySQL(2)-约束的添加及删除

约束条件基本介绍：

约束：
1) 约束保证数据的完整性和一致性
2) 约束分为表级约束和列级约束
列级约束：约束只针对一个字段来使用
表级约束：约束针对两个及两个以上字段来使用
3) 约束类型包括：

• 主键约束（PRIMARY KEY）
• 唯一约束（UNIQUE KEY）
• 默认约束（DEFAULT）
• 非空约束（NOT NULL）
• 外键约束（FOREIGN KEY）（目前仅InnoDB引擎类型支持外键）

各种约束基本用法：

/*************************主键PRIMARY KEY***************************/
1) 每张数据表只能存在一个主键
2) 主键保证记录的唯一性
3) 主键自动为NOT NULL

CREATE TABLE tb3(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL
);

ps:AUTO_INCREMENT一定和主键PRIMARY KEY一起使用，但主键不一定要和AUTO_INCREMENT一起使用,当插入数据时自增,但是若插入失败也会有自增,那么如何防范？答案如下：
ALTER TABLE [表名] MODIFY [列] [新属性1] [新属性2]..;
例如：ALTER TABLE id FROM tb3 MODIFY id SMALLINT UNSIGNED AUTO_INCREMENT;
/*注意*/：主键在创建的同时会自动创建索引

/*************************唯一约束UNIQUE KEY************************/
1) 唯一约束可以保证记录的唯一性
2) 唯一约束的字段可以为空值（NULL）
3) 每张数据表可以存在多个唯一约束

CREATE TABLE tb5(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE KEY,
    age TINYINT UNSIGNED
);//主键为id,唯一约束为username

/*************************默认约束DEFAULT***************************/
CREATE TABLE tb6(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE KEY,
    sex ENUM('1', '2', '3') DEFAULT '3'
);//sex中如果为设定

/*************************外键约束FOREIGN KEY***********************/
保持数据一致性，完整性
实现一对一或一对多关系
/*外键约束的条件*/
1) 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2) 数据表的存储引擎只能为InnoDB
3) 外键列和参照列必须具有相似的数据类型,其中数字的长度或是否有符号位必须相同,而字符长度可不同
4) 外键列和参照列必须创建索引,如果外键列不存在索引,MySQL将自动创建索引

CREATE TABLE provinces(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL,
);

CREATE TABLE users(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) REFERENCES provinces (id)
);
/*外键约束的参照操作*/
1）CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2）SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL.如果使用该选项,必须保证子表列没有指定NOT NULL
3）RESTRICT

CREATE TABLE users1(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE;
);//最后一行属性ON DELETE CASCADE代表若父表删除或更新 则子表中匹配的行也被删除或更新

/*查看mysql当前默认存储引擎*/
SHOW VARIABLES LIKE '%storage_engine%';

/*查看某个表用的引擎(显示结果中engine后面的值为存储引擎)*/
SHOW CREATE TABLE [表名];

/*修改表的存储引擎*/
ALTER TABLE [表名] ENGINE = INNODB;

/*修改默认存储引擎*/
在/etc/my.cnf中加入default-storage-engine=INNODB即可(调整关闭需重启mysql)
ps:如果之前建立的所有数据库表为MyISAM,现在要更改数据库引擎,一般需要一个表一个表的修改,比较繁琐,可以采用先把数据库导出,得到SQL,把MyISAM修改成INNODB,再导入的方式

各种约束的添加删除操作：

/*添加/删除主键约束*/
添加：
ALTER TABLE [表名] ADD [CONSTRAINT [给主键约束起的名字]] PRIMARY KEY (要添加主键约束的属性名);//最后一个属性必须带括号,CONSTRAINT语句可以不用
删除：
ALTER TABLE [表名] DROP PRIMARY KEY;

/*添加/删除唯一约束*/
添加：
ALTER TABLE [表名] ADD [CONSTRAINT [给唯一约束起的名字]] UNIQUE (要添加唯一约束的属性名);//每个表中主键约束只能添加一个,而唯一约束款可以添加多个
删除：
ALTER TABLE [表名] DROP {INDEX | KEY} [属性名];

/*添加/删除外键约束*/
添加：
ALTER TABLE [子表名] ADD [CONSTRAINT [给唯一约束起的名字]] FOREIGN KEY (添加外键约束的属性名) REFERENCES [父表名] (父表属性);
删除：
ALTER TABLE [表名] DROP FOREIGN KEY [外键名];//一般为外键约束中CONSTRAINT命名的名称,不命名的话系统会自动命名,可以通过SHOW CREATE TABLE [表名];查看外键约束名称,在CONSTRAINT之后


/*添加/删除默认约束*/
添加：
ALTER TABLE [表名] ALTER [COLUMN] [属性名] SET DEFAULT [默认值];
删除:
ALTER TABLE [表名] ALTER [COLUMN] [属性名] DROP DEFAULT;

ps:以上添加删除均可用SHOW INDEXES FROM [表名];来查看其对应约束
还需注意的是：
主键一定是唯一性索引，唯一性索引并不一定就是主键
主键列不允许为NULL，而唯一性索引列允许NULL

非空约束、默认约束：只能存在列级约束
主键约束、唯一约束、外键约束：既可以存在于列级约束，也可以存在于表级约束