MySQL外键的基础知识?
外键是一种可用于维护表之间一致性的约束类型。如果我们为表创建一个外键,则该表称为子表,而第二个表将称为父表。
在父表中,外键将充当主键。现在让我们创建一个表。
创建子表。
mysql> create table StudentEnrollment
-> (
-> StudentId int,
-> StudentName varchar(200),
-> StudentFKPK int
-> );
Query OK,0 rows affected (0.91 sec)
阅读更多:MySQL 教程
创建父表
mysql> create table College
-> (
-> StudentFKPK int,
-> CourseId int,
-> CourseName varchar(200),
-> CollegeName varchar(200),
-> primary key(StudentFKPK)
-> );
Query OK,0 rows affected (0.46 sec)
在父表中,列’StudentFKPK’是主键。我们将使用ALTER命令添加外键。
以下是添加外键的语法。
ALTER table yourChildTableName add constraint anyConstraintName
foreign key(primary key column name for parent table)
references College(primary key column name for parent table);
将上述语法实现在以下查询中。
mysql> alter table StudentEnrollment add constraint StudCollegeConst foreign key(StudentFKPK) references College(StudentFKPK);
Query OK,0 rows affected (1.78 sec)
Records: 0,Duplicates: 0,Warnings: 0
检查外键约束是否存在的语法。
SELECT TABLE_NAME, COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NA FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHER REFERENCED_TABLE_NAME = 'yourParentTableName or your referencedTableName';
将上述语法实现在以下查询中。
mysql> SELECT
-> TABLE_NAME,
-> COLUMN_NAME,
-> CONSTRAINT_NAME,
-> REFERENCED_TABLE_NAME,
-> REFERENCED_COLUMN_NAME
-> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE
-> REFERENCED_TABLE_NAME = 'College';
以下是输出结果。
+-------------------+-------------+------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------------+-------------+------------------+-----------------------+------------------------+
| StudentEnrollment | StudentFKPK | StudCollegeConst | College | StudentFKPK |
+-------------------+-------------+------------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.03 sec)
我们有一个名为’StudentFKPK’的列和名为StudCollegeConst的CONSTRAINT_NAME。