Foreign Keys in MySQL

How do I see all foreign keys to a table or column?

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

How to REALLY create a foreign key in MySQL?

I tested your example tables in MySQL Workbench 8.0.28. It successfully created the child table with its foreign key. I ran SHOW CREATE TABLE child and the output shows the foreign key.

But MySQL Workbench apparently has a bug showing the foreign key in the visual table information. I viewed the foreign keys tab as you did. I pressed the "refresh" button and got this message:

Error

Unhandled exception: invalid column constraint_name for resultset

Check the log for more details.

I checked the log (Help->Show Log File) and saw this:

10:15:53 [ERR][sqlide_tableman_ext.py:show_table:1186]: Error initializing tab constraints: Traceback (most recent call last):
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_tableman_ext.py", line 1183, in show_table
    tab.show_table(schema, table)
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_tableman_ext.py", line 854, in show_table
    self.refresh()
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_catalogman_ext.py", line 373, in refresh
    self.preload_data(self.get_query())
  File "/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_catalogman_ext.py", line 367, in preload_data
    node.set_string(i, rset.stringFieldValueByName(field) or "" if format_func is None else format_func(rset.stringFieldValueByName(field)))
SystemError: invalid column constraint_name for resultset

This bug was reported in February 2021: https://bugs.mysql.com/bug.php?id=102496 (and also the duplicate bug ysth links to in the comments above). The bug is "Verified" which means the MySQL team acknowledges it as a bug, but there is no fix and no estimate for when there will be a fix.

how to put foreign key in mysql

No, MySQL parses but ignores the standard inline REFERENCES syntax.

When you declare a foreign key along with an individual column definition, it accepts the syntax as legitimate SQL, but then does not store the foreign key constraint. There's no error reported, but it's as if you didn't write the foreign key syntax at all.

You must declare foreign keys as table-level constraints (your first example above).

This is a case where MySQL is missing a feature of standard SQL. The issue was reported back in 2004, but never fixed! https://bugs.mysql.com/bug.php?id=4919

The reason for this issue is that historically, foreign key constraints were not supported by MySQL itself, but by the InnoDB storage engine, which was made by another company back then. They had to implement their own parser for CREATE TABLE and ALTER TABLE to support foreign keys, and they didn't feel like going the extra steps to support inline foreign key syntax, when table-level foreign key syntax would work.

The architect of InnoDB posted this response:

[6 Sep 2006 10:03] Heikki Tuuri

This will be fixed in MySQL foreign keys, when they are available for all table types.

The MySQL project is gradually working their way toward integrating foreign keys and similar features directly into the MySQL product. Perhaps in a few more years we'll see better support for standard FK syntax.

Foreign keys in MySQL?

Assuming your categories and users table already exist and contain cID and uID respectively as primary keys, this should work:

CREATE TABLE `posts` (
`pID` bigint(20) NOT NULL auto_increment,
`content` text NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uID` bigint(20) NOT NULL,
`wikiptr` bigint(20) default NULL,
`cID` bigint(20) NOT NULL,
PRIMARY KEY  (`pID`),
Foreign Key(`cID`) references categories(`cID`),
Foreign Key(`uID`) references users(`uID`)
) ENGINE=InnoDB;

The column name is required in the references clause.

Foreign key constrains in mysql when trying to add foreign key relationship

Just add DEFAULT CHARSET=latin1. Try out the below query.

    CREATE TABLE activity (
  activity_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  activity_type_id     INT UNSIGNED,
  activity_property_id  INT UNSIGNED,
  added_by             VARCHAR(50) DEFAULT NULL,
  updated_by           VARCHAR(50) DEFAULT NULL,
  activity_code        VARCHAR(50),
  activity_description VARCHAR(100),
  start_date           TIMESTAMP NULL,
  end_date             TIMESTAMP NULL,
  start_time           VARCHAR(10),
  end_time             VARCHAR(10),
  added_date           TIMESTAMP NULL,
  updated_date         TIMESTAMP NULL,
  is_available         TINYINT(1),
  PRIMARY KEY (activity_id),
  CONSTRAINT fk_ADDED_BY_FOR_ACTIVITY FOREIGN KEY (added_by) REFERENCES user (username),
  CONSTRAINT fk_UPDATED_BY_FOR_ACTIVITY FOREIGN KEY (updated_by) REFERENCES user (username)
)
  ENGINE = InnoDB DEFAULT CHARSET=latin1;

MySQL Foreign Keys and Performance Impact

6 updates -- not a big deal. Should be able to handle hundreds of users per second.

If any of those tables are "1:1", then consider combining them. (If they are "1:many", then separate tables is appropriate.)

Indexes are important for performance. A FOREIGN KEY is two things: an index, and a consistency check. Many textbooks push for using FKs; I am usually happy with just the equivalent index.

---

Related Topics