The behavior of foreign keys in "SHOW CREATE TABLE" differs from MySQL. #51837
Closed
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
I'm using parallel_tests with Ruby.
I copy the schema for use, but since foreign keys have schemas, I need to fix the discrepancy in results.
CREATE TABLE `foos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `bars` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`foo_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_xxx` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
SHOW CREATE TABLE bars \G
2. What did you expect to see? (Required)
mysql> SHOW CREATE TABLE bars \G
******************** 1. row ********************
Table: bars
Create Table: CREATE TABLE `bars` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`foo_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_xxx` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
3. What did you see instead (Required)
mysql> SHOW CREATE TABLE bars \G
******************** 1. row ********************
Table: bars
Create Table: CREATE TABLE `bars` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`foo_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_xxx` FOREIGN KEY (`foo_id`) REFERENCES `schema_name`.`foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
4. What is your TiDB version? (Required)
mysql> SELECT tidb_version() \G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:21:44
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)