mysql建立外键案例_mysql 约束和外键约束实例

1、约束保证数据的完整性和一致性。

2、约束分为表级约束和列级约束。(根据约束所针对的字段的数目的多少来决定)

列级约束：对一个数据列建立的约束

表级约束：对多个数据列建立的约束

列级约束即可以在列定义时声明，也可以在列定义后声明，

表级约束只能在列定义后声明，表级约束很少用到。

3、按照功能来划分，约束类型包括

NOT NULL(非空约束)

PRIMARY KEY(主键约束)

UNIQUE KEY(唯一约束)

DEFAULT(默认约束)

FOREIGN KEY(外键约束)

4、外键约束

保持数据一致性，完整性。

实现一对一或者一对多的关系。

实例

1、首先创建一个父表

mysql> CREATE TABLE provinces(

-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

-> pname VARCHAR(20) NOT NULL

-> );

Query OK, 0 rows affected (0.24 sec)

2、查看创建父表用的指令

mysql> SHOW CREATE TABLE provinces;

+----------+-------------------------------------------------------

-------------------------------------------------------------------

----------------------+

| Table | Create Table

|

+----------+-------------------------------------------------------

-------------------------------------------------------------------

----------------------+

| province | CREATE TABLE `province` (

`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`pname` varchar(20) NOT NULL,

PRIMARY KEY (`id`)  #主键在创建的同时会自动的创建索引

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |(满足第一个要求)

+----------+-------------------------------------------------------

-------------------------------------------------------------------

----------------------+

1 row in set (0.02 sec)

3、创建子表

mysql> CREATE TABLE users(

-> id SMALLINT PRIMARY KEY AUTO_INCREMENT,

->

-> username VARCHAR(10) NOT NULL,

-> pid BIGINT,

-> FOREIGN KEY(pid) REFERENCES provinces (id)  #外键pid参照provinces表中的id

-> );

ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150) #外键列必须与子键列有相似的数据类型

mysql>

mysql> CREATE TABLE users(

-> id SMALLINT PRIMARY KEY AUTO_INCREMENT,

-> username VARCHAR(10) NOT NULL,

-> pid SMALLINT UNSIGNED,

-> FOREIGN KEY(pid) REFERENCES province (id)

-> );

Query OK, 0 rows affected (0.17 sec)

有外键的数据表称为子表，子表参照的表称为父表。pid为外键列，id为参照列。

4、显示参照列的索引

mysql> SHOW INDEXES FROM provinces;

+----------+------------+----------+--------------+-------------+-----------+--

----------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | C

rdinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------+------------+----------+--------------+-------------+-----------+--

----------+----------+--------+------+------------+---------+---------------+

| province | 0 | PRIMARY | 1 | id | A |

0 | NULL | NULL | | BTREE | | |

+----------+------------+----------+--------------+-------------+-----------+--

----------+----------+--------+------+------------+---------+---------------+

1 row in set (0.07 sec)

mysql>SHOW INDEXES FROM province\G;  #以网格的形式显示

*************************** 1. row ***************************

Table: province

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:

1 row in set (0.00 sec)

ERROR:

No query specified

5、显示外键列的索引

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:

Index_comment:

*************************** 2. row *************************

Table: users

Non_unique: 1

Key_name: pid  #在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)

ERROR:

No query specified