1.外键约束的要求解析
*** 父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
*** 数据表的存储引擎只能为InnoDB。
*** 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
主键是默认自带索引的 而外键列创建时一般参照的是带有主键那一列 因此如果外键列没有创建索引的话就会被MYSQL根据参照列的索引创建一个索引
*** 外键列是不可以以一个没有索引的列作为参照列的
1.参照列必须要创建一个索引(如果用的是主键默认自带索引 所以不用创建)
2.外键列随意 如果不创建索引会被创建
【1】编辑数据表的默认存储引擎,配置文件/etc/my.cnf
1
2
|
[mysqld]
default-storage-engine=INNODB
|
【2】 创建省份数据表,查看存储引擎
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> USE
test
;
mysql> CREATE TABLE provinces(
->
id
SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
mysql> SHOW CREATE TABLE provinces;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb0 | CREATE TABLE `provinces` (
`
id
` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`
id
`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
【3】创建用户数据表,其中外键列必须和参照列必须有相似的数据类型
1
2
3
4
5
6
|
mysql> CREATE TABLE
users
(
->
id
SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES provinces (
id
)
-> );
|
-
注意:其中有外键列的users表为子表,有参照列id列provinces为父表
-
创建主键时自动创建索引,查看父表自动创建的1个索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> SHOW INDEXES FROM provinces\G;
*************************** 1. row ***************************
Table: provinces
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:
1 row
in
set
(0.00 sec)
ERROR:
No query specified
|
-
若外键列没有索引,自动创建:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql> SHOW INDEXES FROM
users
\G;
*************************** 1. row ***************************
Table:
users
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:
*************************** 2. row ***************************
Table:
users
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:
2 rows
in
set
(0.00 sec)
ERROR:
No query specified
|
2.外键约束的参照操作
CASCADE例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
mysql> CREATE TABLE provinces(
->
id
SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
mysql> 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
-> );
mysql> INSERT provinces(pname) VALUES(
'Tom'
);
mysql> INSERT provinces(pname) VALUES(
'John'
);
mysql> INSERT provinces(pname) VALUES(
'Driver'
);
mysql> INSERT users1(username,pid) VALUES(
'Huang'
,2);
mysql> INSERT users1(username,pid) VALUES(
'Li'
,3);
mysql> INSERT users1(username,pid) VALUES(
'Pan'
,3);
mysql> INSERT users1(username,pid) VALUES(
'He'
,1);
mysql> INSERT users1(username,pid) VALUES(
'Long'
,2);
mysql> SELECT * FROM users1;
+----+----------+------+
|
id
| username | pid |
+----+----------+------+
| 1 | Huang | 2 |
| 2 | Li | 3 |
| 5 | Pan | 3 |
| 6 | He | 1 |
| 7 | Long | 2 |
+----+----------+------+
mysql> SELECT * FROM provinces;
+----+--------+
|
id
| pname |
+----+--------+
| 1 | Tom |
| 2 | John |
| 3 | Driver |
+----+--------+
mysql> DELETE FROM provinces WHERE
id
=3;
mysql> SELECT * FROM provinces;
+----+-------+
|
id
| pname |
+----+-------+
| 1 | Tom |
| 2 | John |
+----+-------+
mysql> SELECT * FROM users1;
+----+----------+------+
|
id
| username | pid |
+----+----------+------+
| 1 | Huang | 2 |
| 6 | He | 1 |
| 7 | Long | 2 |
+----+----------+------+
|
3.表级约束和列级约束
4.修改数据表
1
|
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
|
1
2
3
4
5
6
7
8
|
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
|
id
| smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
|
1
2
3
4
5
6
7
8
9
10
|
mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
|
id
| smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
|
id
| smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> ALTER TABLE users1 ADD truename VARCHAR(32) NOT NULL FIRST;
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| truename | varchar(32) | NO | | NULL | |
|
id
| smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
|
删除列:
1
|
ALTER TABLE tbl_name(数据表名称) DROP [COLUMN] col_name(列名)
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> ALTER TABLE users1 DROP truename;
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
|
id
| smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
+----------+----------------------+------+-----+---------+----------------+
|
1
2
3
4
5
6
7
8
9
|
mysql> ALTER TABLE users1 DROP age,DROP password;
mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
|
id
| smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
|
添加 / 删除主键约束:
1
2
3
|
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
//
添加
ALTER TABLE tbl_name DROP PRIMARY KEY
//
删除
|
添加 / 删除唯一约束:
1
2
3
|
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
//
添加
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
//
删除
|
添加 / 删除外键约束:
1
2
3
|
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
//
添加
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
//
删除
|
添加 / 删除默认约束:
1
2
|
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}
//
添加
mysql> ALTER TABLE tbl_name ALTER age DROP DEFAULT;
//
删除
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> CREATE TABLE users2(
-> username VARCHAR(20) NOT NULL,
-> pid SMALLINT UNSIGNED
-> );
mysql> ALTER TABLE users2 ADD
id
SMALLINT UNSIGNED;
mysql> ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (
id
);
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
|
id
| smallint(5) unsigned | NO | PRI | 0 | |
+----------+----------------------+------+-----+---------+-------+
|
1
|
mysql> ALTER TABLE users2 DROP PRIMARY KEY;
|
添加唯一约束:添加username列为唯一约束:
1
|
mysql> ALTER TABLE users2 ADD CONSTRAINT OK_users2_username UNIQUE KEY (username);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
mysql> SHOW INDEXES FROM users2\G;
*************************** 1. row ***************************
Table: users2
Non_unique: 0
Key_name: OK_users2_username
Seq_in_index: 1
Column_name: username
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: users2
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:
2 rows
in
set
(0.00 sec)
ERROR:
No query specified
mysql> ALTER TABLE users2 DROP INDEX OK_users2_username;
mysql> SHOW INDEXES FROM users2\G;
*************************** 1. row ***************************
Table: users2
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:
1 row
in
set
(0.00 sec)
|
1
|
mysql> ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (
id
);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SHOW CREATE TABLE users2;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
`username` varchar(20) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`
id
` smallint(5) unsigned NOT NULL DEFAULT
'0'
,
`age` tinyint(4) NOT NULL,
KEY `pid` (`pid`),
CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`
id
`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> ALTER TABLE users2 ADD age TINYINT NOT NULL;
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
|
id
| smallint(5) unsigned | NO | PRI | 0 | |
| age | tinyint(4) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
|
id
| smallint(5) unsigned | NO | PRI | 0 | |
| age | tinyint(4) | NO | | 15 | |
+----------+----------------------+------+-----+---------+-------+
|
1
2
3
4
5
6
7
8
9
10
|
mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;
mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | MUL | NULL | |
|
id
| smallint(5) unsigned | NO | PRI | 0 | |
| age | tinyint(4) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
|
修改列定义:
1
|
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
|
修改列名称(不能随便更改):
1
|
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_dufinition [FIRST|AFTER col_name]
|
修改数据表名字(不能随便更改):
1
|
ALTER TABLE old_tbl_name RENAME new_tbl_name
|
方法二:
1
|
RENAME TABLE old_tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...
|