mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
94 lines
2.6 KiB
Text
94 lines
2.6 KiB
Text
create database bug_fk;
|
|
use bug_fk;
|
|
CREATE TABLE b (
|
|
b int unsigned NOT NULL,
|
|
d1 datetime NOT NULL,
|
|
PRIMARY KEY (b,d1)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE c (
|
|
b int unsigned NOT NULL,
|
|
d1 datetime NOT NULL,
|
|
d2 datetime NOT NULL,
|
|
PRIMARY KEY (b,d1),
|
|
CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b)
|
|
) ENGINE=InnoDB;
|
|
show warnings;
|
|
Level Code Message
|
|
set foreign_key_checks = 0;
|
|
DROP TABLE IF EXISTS b;
|
|
show create table c;
|
|
Table Create Table
|
|
c CREATE TABLE `c` (
|
|
`b` int(10) unsigned NOT NULL,
|
|
`d1` datetime NOT NULL,
|
|
`d2` datetime NOT NULL,
|
|
PRIMARY KEY (`b`,`d1`),
|
|
CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
CREATE TABLE b (
|
|
b bigint unsigned NOT NULL,
|
|
d1 date NOT NULL,
|
|
PRIMARY KEY (b,d1)
|
|
) ENGINE=InnoDB;
|
|
DROP TABLE b;
|
|
set foreign_key_checks = 1;
|
|
CREATE TABLE b (
|
|
b bigint unsigned NOT NULL,
|
|
d1 date NOT NULL,
|
|
PRIMARY KEY (b,d1)
|
|
) ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1005 Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
Warning 1215 Cannot add foreign key constraint for `b`
|
|
set foreign_key_checks = 0;
|
|
DROP TABLE IF EXISTS d;
|
|
Warnings:
|
|
Note 1051 Unknown table 'bug_fk.d'
|
|
CREATE TABLE d (
|
|
b bigint unsigned NOT NULL,
|
|
d1 date NOT NULL,
|
|
PRIMARY KEY (b,d1),
|
|
CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b)
|
|
) ENGINE=InnoDB;
|
|
show warnings;
|
|
Level Code Message
|
|
set foreign_key_checks = 1;
|
|
show create table c;
|
|
Table Create Table
|
|
c CREATE TABLE `c` (
|
|
`b` int(10) unsigned NOT NULL,
|
|
`d1` datetime NOT NULL,
|
|
`d2` datetime NOT NULL,
|
|
PRIMARY KEY (`b`,`d1`),
|
|
CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
show create table d;
|
|
Table Create Table
|
|
d CREATE TABLE `d` (
|
|
`b` bigint(20) unsigned NOT NULL,
|
|
`d1` date NOT NULL,
|
|
PRIMARY KEY (`b`,`d1`),
|
|
CONSTRAINT `bd_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
CREATE TABLE b (
|
|
b bigint unsigned NOT NULL,
|
|
d1 date NOT NULL,
|
|
PRIMARY KEY (b,d1)
|
|
) ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1005 Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
Warning 1215 Cannot add foreign key constraint for `b`
|
|
set foreign_key_checks=0;
|
|
drop table c;
|
|
drop table d;
|
|
create table b(id int) engine=innodb;
|
|
show warnings;
|
|
Level Code Message
|
|
b.frm
|
|
b.ibd
|
|
drop table b;
|
|
drop database bug_fk;
|