外键约束 FOREIGN KEY

 

https://blog.csdn.net/feiyucity/article/details/82974671 

约束保证数据的完整和一致性 ，分为表级约束列级约束 ；

                                                     假设约束之针对某一个字段使用称为列级约束；

                                                     假设约束针对两个或两个以上的字段使用称之为表级约束；

按照约束功能类型来分有：

                                          NOT NULL 非空约束；

                                          PRIMARY KEY 主键约束；

                                          UNIQUE KEY 唯一约束；

                                          DEFAULT 默认约束；

                                          FOREIGN KEY 外键约束；

外键约束 ——————为实现一对一 或多关系群体，可以保持记录一致性，完整性；
 
要求：
     （1）夫表和子表必须使用相同的存储引擎 ， 禁止使用临时表 ;
      (2) 数据表的存储引擎必须为InnoDB ;
     （3）外键列和参照列必须具有相似数据类型；
      (4)外键列和参照列必需创建索引，相反，如果外键列不存在索引的话，MySQL会自动创建索引；
 
 
更改MySQL配置文件:
                default - storage - engine = INNoDB; 修改完配置文件 重启MySQL 相关服务才可以；
      
                                                 创建省份表
mysql> CREATE TABLE province(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> SHOW CREATE TABLE province ;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
| Table    | Create Table
 
                      |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
| province | CREATE TABLE `province` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |               ENGINE=MyISAM这里更改INNoDB；
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
1 row in set (0.01 sec)
                           当INNoDB未更改情况下 (pid BIGINT)此段代码正确，假设更改后码段错误
mysql> CREATE TABLE users(  
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY (pid) REFERENCES province(id)
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT,
    -> FOREIGN KEY (pid) REFERENCES province(id)
    -> );
ERROR 1050 (42S01): Table 'users' already exists
                        当INNoDB以更改情况下 (pid SMALLINT)此段代码正确，假设更改后码段错误
 
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES province(id)
    -> );
ERROR 1050 (42S01): Table 'users' already exists
mysql>
     
mysql> SHOW CREATE TABLE province ;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
| Table    | Create Table
 
                      |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
| province | CREATE TABLE `province` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
1 row in set (0.01 sec)
 
mysql> CRREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY (pid) REFERENCES province(id)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'CRREA
TE TABLE users(
id SMALLINT UNSIGNED PRIMARY AUTO_INCREMENT,
username VARCH' at line 1
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY (pid) REFERENCES province(id)
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT,
    -> FOREIGN KEY (pid) REFERENCES province(id)
    -> );
ERROR 1050 (42S01): Table 'users' already exists
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES province(id)
    -> );
ERROR 1050 (42S01): Table 'users' already exists
mysql> SHOW INDEXES FROM province;
+----------+------------+----------+--------------+-------------+-----------+---
----------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Ca
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.00 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
 
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.01 sec)
 
ERROR:
No query specified
 
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
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
 
ERROR:
No query specified
                                     [id为自行添加 pid系统自行创建（还是不太理解）]
 
mysql> SHOW CREATE TABLE users;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------------+
| Table | Create Table
 
                                                                         |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),                                                  （MyISAM）当前是
  KEY `pid` (`pid`)                                  在INNoDB情况下 系统已经自己创建啦pid
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------------+
1 row in set (0.00 sec)