MySQL如何检查外键约束是否存在？

Question

如果我有：

create table order_items
( 
  orderid int unsigned not null references orders(orderid),
  isbn char(13) not null,
  quantity tinyint unsigned,

  primary key (orderid, isbn)

);

我该如何检查是否存在 orderid int unsigned not null references orders(orderid) 外键？

- Robert

---

请查看：21.17 INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS 表。 - wchiquito

如果下面的答案涵盖了问题主题，请接受它。 - Rolice

Answer 1

您可以像这样使用INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS：

SELECT *
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '<schema>'
AND REFERENCED_TABLE_NAME = 'order_items'
AND CONSTRAINT_NAME = 'orderid'

- Beginner

Answer 2

您可以使用此功能检查整个数据库的外键约束。

SELECT TABLE_NAME ,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE

如果需要检查特定的表格，请在上面的查询中添加此where条件。

WHERE
    REFERENCED_TABLE_SCHEMA = 'student' AND REFERENCED_TABLE_NAME = 'student_details'

- vinoth

Answer 3

IF EXISTS(
    SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE REFERENCED_TABLE_NAME = 'referenceTableName' AND TABLE_SCHEMA  = 'schemaName' AND TABLE_NAME = 'mainTableName' AND CONSTRAINT_NAME = 'constraintName'
)THEN
    //Execute query whatever you need
END IF;

- Radadiya Nikunj