MySQL基础用法之约束

一、约束分类

1、按功能划分

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

2、按数据列数据划分

• 列级约束：对一个数据列建立的约束，称为列级约束。既可以在定义列时声明，也可以在定义列后声明。实际使用较多。

• 表级约束：对多个数据列建立的约束，称为表级约束。需要在定义列后声明。不存在NOT NULL和DEFAULT约束。

二、外键约束的参照操作

1、CASCADE

      从父表删除或更新数据后，自动删除或更新子表中匹配的行。

2、SET NULL

      从父表删除或更新数据后，设置子表中的外键列为NULL，前提是必须保证子表的外键列没有设置NOT NULL。

3、RESTRICT/NO ACTION

      拒绝对父表进行删除或更新操作。

三、添加约束

      首先创建一个没有约束的表

CREATE TABLE users(
    id SMALLINT UNSIGNED,
    age TINYINT UNSIGNED,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED
);

     1、 添加主键约束，symbol表示约束的名字

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name);

      例如

ALTER TABLE users ADD CONSTRAINT PK_users_id PRIMARY KEY (id);

      2、添加唯一约束，与主键约束不同的是可以添加多个

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

      例如

ALTER TABLE users ADD UNIQUE (username);

      3、添加外键约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name](index_col_name,...) reference_definition;

      例如，users表中的pid字段需要与table2中的id字段有相同的数据类型。

ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCE table2 (id);

      4、添加默认约束

ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal};

      例如

ALTER TABLE users ALTER age SET DEFAULT 15;

五、删除约束

      1、删除主键约束

ALTER TABLE table_name DROP PRIMARY KEY;

      2、删除唯一约束

ALTER TABLE table_name DROP {INDEX|KEY} index_name;

       例如

ALTER TABLE users DROP INDEX username;

      3、删除外键约束

ALTER TABLE table_name DROP FOREIGN KEY fk_symbol;

      fk_symbol为外键约束的名字，通过下面命令，可查询外键约束的名字

SHOW CREATE TABLE users;

      4、删除默认约束

ALTER TABLE table_name ALTER [COLUMN] col_name {DROP DEFAULT};

      例如

ALTER TABLE users ALTER age DROP DEFAULT;