MySQL---外键、添加删除约束、ON DELETE参照、添加|删除|修改数据

外键(FOREIGN KEY)

外键约束需要满足一些基本要求：

• 父表和子表必须是相同的引擎，而且禁止使用临时表
• 数据表的存储引擎只能是InnoDB
• 外键列和参照列必须具有相似的数据烈性。其中数字的长度或是否有符号为必须相同。而字符串的长度可以不同。
• 外键列和参照列必须创建索引。如果外键列不存在索引的话，MaSQL将自动创建索引。

显示索引，test表由于同时有主键和外键，所以有两个索引

mysql> SHOW INDEX FROM user \G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

ON DELETE 参照操作

• CASCADE 从父表删除或更新，自动删除或更新字表中匹配的行
• SET NULL 从父表删除或更新行，并设置字表中的外键列为NULL。如果使用该选项必须保障字表列没有指定NOT NULL
• RESTRICT 拒绝对父表的删除或更新曹祖
• NO ACTION 标准的SQL关键字，在MySQL中与RESTRICT相同

mysql> CREATE TABLE user2 (
    -> id SMALLINT  UNSIGNED PRIMARY KEY ,
    -> name VARCHAR(20) NOT NULL ,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
    -> );

表级约束与列级约束

• 对一个数据列建立的约束，成为列级约束
• 对多个数据列建立的约束，成为标记约束。
• 劣迹约束既可以在列定义时声名，也可以在列定义后声名
• 表级约束只能在列定义后声名

常见的DEFAULT 和 NOT NULL是列级约束

修改表约束

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_name]] PRIMARY KEY [index_type] (index_col_name,…)

 ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (id);

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_col_name]] UNIQUE [INDEX|KEY] [index_name][index_type](index_col_name,…)

 ALTER TABLE test ADD CONSTRAINT unique_test UNIQUE KEY(name) ;

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [constraint_name]] FOREIGN KEY [index_name](index_col_name,…) REFERENCES table_name(col_name)

mysql> ALTER TABLE test0 ADD CONSTRAINT fk_test0 FOREIGN KEY (data) REFERENCES test(id);

添加、删除默认约束

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

// 添加默认约束
mysql> ALTER TABLE test ALTER COLUMN name SET DEFAULT 'TOM';
// 删除默认约束
mysql> ALTER TABLE test ALTER COLUMN name DROP DEFAULT;

删除主键约束

**ALTER TABLE tbl_name DROP PRIMARY KEY **
删除主键时，如果主键有AUTO_INCREMENT约束，就不可以删除该主键约束，必须要先删除AUTO_INCREMENT，才可以删除主键约束。因为数据库将具有AUTO_INCREMENT约束的列看成主键

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

// 先查看唯一约束的名字
mysql> SHOW CREATE TABLE test ;
| test  | CREATE TABLE `test` (
  `id` int(50) NOT NULL,
  `name` varchar(10),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_test` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

// 根据唯一约束的名字删除对应的约束
mysql> ALTER TABLE test DROP INDEX unique_test;

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_name

mysql> ALTER TABLE test0 DROP FOREIGN KEY fk_test0;

查看索引

**SHOW INDEX FROM tbl_name **

mysql> SHOW INDEX FROM test0 ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test0 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test0 |          1 | fk_test0 |            1 | data        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

\G 将查询结果按列输出

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

mysql> CREATE TABLE user (
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL ,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );

查看表信息

SHOW CREATE TABLE tbl_name

mysql> show create table test0 ;
| test0 | CREATE TABLE `test0` (
  `id` int(50) NOT NULL,
  `data` int(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test0` (`data`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

修改数据表

添加、删除列

添加列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [(FIRST) | (AFTER col_name)]

// 插入一条记录
ALTER TABLE tb4 ADD COLUMN age INT(10) UNSIGNED DEFAULT 10 ;

// 在最前面插入一条记录
ALTER TABLE tb4 ADD COLUMN truename VARCHAR(20) NOT NULL FIRST;

// 在sex列后面插入一条记录
mysql> ALTER TABLE tb4 ADD COLUMN password VARCHAR(20) AFTER sex ;

添加多列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition (,ADD [column] col_name2 column_definetion…)

// 添加school、和grade列
mysql> ALTER TABLE test ADD COLUMN school VARCHAR(20),ADD COLUMN grade FLOAT;

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

// 删除grade列
mysql> ALTER TABLE test DROP COLUMN grade ;

同时使用多个操作

ALTER TABLE tbl_name DROP(|ADD) [COLUMN] col_name,DROP(|ADD) [COLUMN] col_name2,…

// 删除grade列 ，添加school列
mysql> ALTER TABLE test ADD COLUMN grade FLOAT ,DROP COLUMN school ;

修改数据表列定义

• **ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|ALTER col_name] **
  在不改变列名的情况下修改列定义

mysql> ALTER TABLE test MODIFY name VARCHAR(20) DEFAULT 'JERRY';
mysql> DESCRIBE test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | JERRY   |                |
| sex   | varchar(5)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

新的参数中如果没有包含原来的约束，那么原来的约束会被删除

mysql> ALTER TABLE test MODIFY name VARCHAR(30) ;
mysql> DESCRIBE test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| sex   | varchar(5)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

• **ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] **
  可以修改列名，和列定义

mysql> ALTER TABLE test CHANGE name new_name VARCHAR(20) DEFAULT 'TOM' FIRST ;
mysql> DESCRIBE test;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| new_name | varchar(20) | YES  |     | TOM     |       |
| id       | int(20)     | NO   | PRI | NULL    |       |
| sex      | varchar(5)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

修改数据表名称

• ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name 修改单个表名称
• RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2…] 修改多个表名称

// 修改单个表名称
mysql> ALTER TABLE test RENAME TO  db_test ;

// 修改多个表名称
mysql> RENAME TABLE db_test TO test ,tb4 TO tb5 ;