Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
11.4.2
-
None
-
11.4.2-MariaDB-deb12
Description
CREATE TABLE `Tab1` ( |
`T1Id` int(11) NOT NULL, |
`T1ExtId` varchar(60) NOT NULL, |
PRIMARY KEY (`T1Id`), |
UNIQUE KEY `UIdxTab1T1ExtId` (`T1ExtId`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; |
|
CREATE TABLE `Tab3` ( |
`T3Id` int(11) NOT NULL, |
`T3Str` varchar(60) NOT NULL, |
`T3Num` int(11) NOT NULL, |
`T1Id` int(11) NOT NULL, |
PRIMARY KEY (`T3Id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; |
|
CREATE TABLE `Tab4` ( |
`T4Id` int(11) NOT NULL, |
`T4Str` varchar(60) NOT NULL, |
`T3Id` int(11) NOT NULL, |
PRIMARY KEY (`T4Id`), |
KEY `FkTab4T3Id` (`T3Id`), |
CONSTRAINT `FkTab4T3Id` FOREIGN KEY (`T3Id`) REFERENCES `Tab3` (`T3Id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; |
insert some data
insert into Tab1 (T1Id, T1ExtId) values (1, 'one'), (2, 'two'); |
insert into Tab3 (T3Id, T3Str, T3Num, T1Id) values (1, 'one', 1, 1), (2, 'two', 11, 2), (3, 'three', 1111, 1), (4, 'four', 11111, 2); |
|
insert into Tab3 (T3Id, T3Str, T3Num, T1Id) values (5, 'one', 1, 1); |
adding foreign key constraint and unique index in one statement
alter table Tab3 |
add constraint FkTab3T1Id foreign key (T1Id) references Tab1 (T1Id), |
add unique index UIdxTab3T3Str (T3Str); |
My last insert is a dupplicate value "one" in col T3Str
But error message is
Cannot delete rows from table which is parent in a foreign key constraint 'FkTab4T3Id' of table 'Tab4'
|
alter statement seperates in two statements
alter table Tab3 add constraint FkTab3T1Id foreign key (T1Id) references Tab1 (T1Id); |
alter table Tab3 add unique index UIdxTab3T3Str (T3Str); |
brings a correkt error message
Duplicate entry 'one' for key 'UIdxTab3T3Str'
|
If foreign key contraint Tab4 -> Tab3.T3Id is not present, the error message is correkt with combined alter statement with foreign key and unique index.
Attachments
Issue Links
- duplicates
-
MDEV-33927 Wrong error message for the alter statement failure
- Open