mirror of
https://github.com/MariaDB/server.git
synced 2025-02-10 07:34:11 +01:00
573 lines
20 KiB
Text
573 lines
20 KiB
Text
#################
|
|
# Test RESTRICT #
|
|
#################
|
|
create table parent(
|
|
id int,
|
|
KEY_TYPE (id)
|
|
) engine innodb;
|
|
create table child(
|
|
parent_id int,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end),
|
|
foreign key(parent_id) references parent(id)
|
|
on delete restrict
|
|
on update restrict
|
|
) engine innodb with system versioning;
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
delete from parent where id = 1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
delete from child where parent_id = 1;
|
|
delete from parent where id = 1;
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
update parent set id=id+1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
delete from child;
|
|
update parent set id=id+1;
|
|
select * from child for system_time all;
|
|
parent_id
|
|
1
|
|
1
|
|
drop table child;
|
|
drop table parent;
|
|
##############################################
|
|
# Test when clustered index is a foreign key #
|
|
##############################################
|
|
create table parent(
|
|
id int(10) unsigned,
|
|
KEY_TYPE (id)
|
|
) engine innodb;
|
|
create table child(
|
|
parent_id int(10) unsigned primary key,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end),
|
|
foreign key(parent_id) references parent(id)
|
|
) engine innodb with system versioning;
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
delete from parent where id = 1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
drop table child;
|
|
drop table parent;
|
|
################
|
|
# Test CASCADE #
|
|
################
|
|
create table parent(
|
|
id int,
|
|
KEY_TYPE (id)
|
|
) engine innodb;
|
|
create table child(
|
|
parent_id int,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end),
|
|
foreign key(parent_id) references parent(id)
|
|
on delete cascade
|
|
on update cascade
|
|
) engine innodb with system versioning;
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
delete from parent where id = 1;
|
|
select * from child;
|
|
parent_id
|
|
select * from child for system_time all;
|
|
parent_id
|
|
1
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
update parent set id = id + 1;
|
|
select * from child;
|
|
parent_id
|
|
2
|
|
select * from child for system_time all;
|
|
parent_id
|
|
1
|
|
1
|
|
2
|
|
drop table child;
|
|
drop table parent;
|
|
create or replace table parent (
|
|
id int,
|
|
KEY_TYPE(id),
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end)
|
|
) with system versioning
|
|
engine innodb;
|
|
create or replace table child (
|
|
x int,
|
|
parent_id int not null,
|
|
constraint `parent-fk`
|
|
foreign key (parent_id) references parent (id)
|
|
on delete cascade
|
|
on update restrict
|
|
)
|
|
engine innodb;
|
|
insert into parent (id) values (2);
|
|
insert into child (x, parent_id) values (2, 2);
|
|
delete from parent;
|
|
select * from child;
|
|
x parent_id
|
|
drop table child;
|
|
drop table parent;
|
|
create or replace table parent (
|
|
id int,
|
|
KEY_TYPE(id)
|
|
)
|
|
engine innodb;
|
|
create or replace table child (
|
|
id int primary key,
|
|
parent_id int not null,
|
|
row_start SYS_DATATYPE as row start invisible,
|
|
row_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(row_start, row_end),
|
|
constraint `parent-fk`
|
|
foreign key (parent_id) references parent (id)
|
|
on delete cascade
|
|
on update restrict
|
|
) with system versioning
|
|
engine innodb;
|
|
insert into parent (id) values (3);
|
|
insert into child (id, parent_id) values (3, 3);
|
|
delete from parent;
|
|
select * from child;
|
|
id parent_id
|
|
select *, check_row(row_start, row_end) from child for system_time all;
|
|
id parent_id check_row(row_start, row_end)
|
|
3 3 HISTORICAL ROW
|
|
drop table child;
|
|
drop table parent;
|
|
#################
|
|
# Test SET NULL #
|
|
#################
|
|
create table parent(
|
|
id int,
|
|
KEY_TYPE (id)
|
|
) engine innodb;
|
|
create or replace table child(
|
|
parent_id int,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end),
|
|
foreign key(parent_id) references parent(id)
|
|
on delete set null
|
|
on update set null
|
|
) engine innodb with system versioning;
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
delete from child;
|
|
insert into child values(1);
|
|
delete from parent where id = 1;
|
|
select * from child;
|
|
parent_id
|
|
NULL
|
|
select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
|
|
parent_id current_row
|
|
1 0
|
|
1 0
|
|
NULL 1
|
|
delete from child;
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
update parent set id= id + 1;
|
|
select * from child;
|
|
parent_id
|
|
NULL
|
|
select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
|
|
parent_id current_row
|
|
1 0
|
|
1 0
|
|
NULL 0
|
|
1 0
|
|
NULL 1
|
|
drop table child;
|
|
drop table parent;
|
|
###########################
|
|
# Parent table is foreign #
|
|
###########################
|
|
create or replace table parent(
|
|
id int,
|
|
KEY_TYPE (id),
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end)
|
|
) engine innodb with system versioning;
|
|
create or replace table child(
|
|
parent_id int,
|
|
foreign key(parent_id) references parent(id)
|
|
) engine innodb;
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
delete from parent;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
update parent set id=2;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
delete from child;
|
|
delete from parent;
|
|
insert into child values(1);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
insert into parent values(1);
|
|
insert into child values(1);
|
|
delete from parent;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
update parent set id=2;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
|
|
drop table child;
|
|
drop table parent;
|
|
###################
|
|
# crash on DELETE #
|
|
###################
|
|
create or replace table a (
|
|
cola int(10),
|
|
KEY_TYPE (cola),
|
|
v_cola int(10) as (cola mod 10) virtual,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end)
|
|
) engine=innodb with system versioning;
|
|
create index v_cola on a (v_cola);
|
|
create or replace table b(
|
|
cola int(10),
|
|
v_cola int(10),
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end)
|
|
) engine=innodb with system versioning;
|
|
alter table b add constraint `v_cola_fk`
|
|
foreign key (v_cola) references a (v_cola);
|
|
insert into a(cola) values (12);
|
|
insert into b(cola, v_cola) values (10,2);
|
|
delete from a;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`))
|
|
drop table b, a;
|
|
###############################################
|
|
# CASCADE UPDATE foreign not system versioned #
|
|
###############################################
|
|
create or replace table parent (
|
|
id smallint unsigned not null auto_increment,
|
|
value int unsigned not null,
|
|
primary key (id, value)
|
|
) engine = innodb;
|
|
create or replace table child (
|
|
id mediumint unsigned not null auto_increment primary key,
|
|
parent_id smallint unsigned not null,
|
|
parent_value int unsigned not null,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time(sys_start, sys_end),
|
|
constraint `fk_child_parent`
|
|
foreign key (parent_id, parent_value) references parent (id, value)
|
|
on delete cascade
|
|
on update cascade
|
|
) engine = innodb with system versioning;
|
|
create or replace table subchild (
|
|
id int not null auto_increment primary key,
|
|
parent_id smallint unsigned not null,
|
|
parent_value int unsigned not null,
|
|
constraint `fk_subchild_child_parent`
|
|
foreign key (parent_id, parent_value) references child (parent_id, parent_value)
|
|
on delete cascade
|
|
on update cascade
|
|
) engine=innodb;
|
|
insert into parent (value) values (23);
|
|
select id, value from parent into @id, @value;
|
|
Warnings:
|
|
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
|
|
insert into child values (default, @id, @value);
|
|
insert into subchild values (default, @id, @value);
|
|
select parent_id from subchild;
|
|
parent_id
|
|
1
|
|
update parent set id = 11, value = value + 1;
|
|
select parent_id from subchild;
|
|
parent_id
|
|
11
|
|
select * from child;
|
|
id parent_id parent_value
|
|
1 11 24
|
|
delete from parent;
|
|
select count(*) from child;
|
|
count(*)
|
|
0
|
|
select * from child for system_time all;
|
|
id parent_id parent_value
|
|
1 1 23
|
|
1 11 24
|
|
select count(*) from subchild;
|
|
count(*)
|
|
0
|
|
drop table subchild, child, parent;
|
|
#
|
|
# MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation
|
|
#
|
|
create or replace table t1 (f1 int, key(f1)) engine=innodb;
|
|
create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning;
|
|
set foreign_key_checks= off;
|
|
insert ignore into t2 values (1);
|
|
set foreign_key_checks= on;
|
|
update t2 set f2= 2;
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`))
|
|
delete from t2;
|
|
drop table t2, t1;
|
|
#
|
|
# MDEV-18879 Corrupted record inserted by FOREIGN KEY operation
|
|
#
|
|
SET timestamp = 1;
|
|
SET time_zone='+02:00';
|
|
SELECT now();
|
|
now()
|
|
1970-01-01 02:00:01
|
|
CREATE TABLE t1 (
|
|
pk INT UNSIGNED PRIMARY KEY,
|
|
f1 varchar(255) CHARACTER SET ucs2,
|
|
f2 longtext CHARACTER SET ucs2,
|
|
f3 varchar(255),
|
|
f4 char(255),
|
|
f5 longtext CHARACTER SET ucs2,
|
|
f6 INT UNSIGNED,
|
|
f7 INT UNSIGNED,
|
|
f8 INT UNSIGNED,
|
|
f9 INT UNSIGNED,
|
|
f10 INT UNSIGNED,
|
|
f11 INT UNSIGNED,
|
|
f12 varchar(255) CHARACTER SET ucs2,
|
|
f13 char(255) CHARACTER SET ucs2,
|
|
f14 char(255) CHARACTER SET ucs2,
|
|
f15 varchar(255),
|
|
f16 longtext,
|
|
f17 char(255)
|
|
) ENGINE=InnoDB WITH SYSTEM VERSIONING;
|
|
INSERT INTO t1 VALUES
|
|
(1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'),
|
|
(2, REPEAT('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, REPEAT('o',222), 'oven', 'flower', REPEAT('r',120), 'l', 'g'),
|
|
(3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'),
|
|
(4, 'r', 't', REPEAT('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, REPEAT('g',128), 'a', 'w', 'f', 'b', 'b'),
|
|
(5, 'h', 'y', REPEAT('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', REPEAT('n',149), 'folk', 'd'),
|
|
(6, 'g', 'violent', REPEAT('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'),
|
|
(7, 'k', 'uncomfortable', REPEAT('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, REPEAT('j',204), 'j', 'statute', 'emphasis', 'u', 'water'),
|
|
(8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'),
|
|
(9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'),
|
|
(10, REPEAT('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'),
|
|
(11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility');
|
|
CREATE TABLE t2 (
|
|
pk int primary key,
|
|
f char(255) CHARACTER SET ucs2,
|
|
key(f)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (1,'against'),(2,'q');
|
|
SET SQL_MODE= '';
|
|
SET timestamp = 2;
|
|
SELECT * INTO OUTFILE 't1.data' FROM t1;
|
|
SET timestamp = 3;
|
|
UPDATE t1 SET f13 = 'q';
|
|
SET timestamp = 4;
|
|
LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'f12' at row 2
|
|
Warning 1265 Data truncated for column 'f12' at row 4
|
|
Warning 1265 Data truncated for column 'f12' at row 7
|
|
SELECT * INTO OUTFILE 't1.data.2' FROM t1;
|
|
SET timestamp = 5;
|
|
LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1;
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'f1' at row 2
|
|
Warning 1265 Data truncated for column 'f12' at row 2
|
|
Warning 1265 Data truncated for column 'f12' at row 4
|
|
Warning 1265 Data truncated for column 'f12' at row 7
|
|
Warning 1265 Data truncated for column 'f1' at row 10
|
|
SELECT * INTO OUTFILE 't2.data' FROM t2;
|
|
SET timestamp = 6;
|
|
LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2;
|
|
SET FOREIGN_KEY_CHECKS = OFF;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (f13) REFERENCES t2 (f) ON DELETE SET NULL;
|
|
SET timestamp = 7;
|
|
LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'f12' at row 2
|
|
Warning 1265 Data truncated for column 'f12' at row 4
|
|
Warning 1265 Data truncated for column 'f12' at row 7
|
|
SET FOREIGN_KEY_CHECKS = ON;
|
|
SET SESSION SQL_MODE= 'NO_BACKSLASH_ESCAPES';
|
|
SET timestamp = 8;
|
|
LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'f12' at row 2
|
|
Warning 1265 Data truncated for column 'f12' at row 4
|
|
Warning 1265 Data truncated for column 'f12' at row 7
|
|
SET timestamp = 9;
|
|
REPLACE INTO t2 SELECT * FROM t2;
|
|
DROP TABLE t1, t2;
|
|
set timestamp= default;
|
|
set time_zone='+00:00';
|
|
#
|
|
# MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation
|
|
#
|
|
create or replace table t1 (a int, key(a)) engine innodb with system versioning;
|
|
create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb;
|
|
insert into t1 values (1),(2);
|
|
insert into t2 values (1);
|
|
# DELETE from referenced table is not allowed
|
|
delete from t1 where a = 1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
|
|
drop tables t2, t1;
|
|
#
|
|
# MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or server crash in row_ins_foreign_report_err upon DELETE from versioned table with FK
|
|
#
|
|
create or replace table t1 (x int primary key) engine innodb;
|
|
create or replace table t2 (x int, foreign key (x) references t1(x)) engine innodb with system versioning;
|
|
set foreign_key_checks= off;
|
|
insert into t2 values (1), (1);
|
|
set foreign_key_checks= on;
|
|
# DELETE from foreign table is allowed
|
|
delete from t2;
|
|
drop tables t2, t1;
|
|
create or replace table t1 (a int, key(a)) engine innodb;
|
|
insert into t1 values (1);
|
|
create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb with system versioning;
|
|
insert into t2 values (1), (1);
|
|
# DELETE from foreign table is allowed
|
|
delete from t2;
|
|
drop tables t2, t1;
|
|
#
|
|
# MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table
|
|
#
|
|
create table t1 (pk int primary key, f1 int,f2 int, f3 text,
|
|
key(f1), fulltext(f3), key(f3(10)),
|
|
foreign key (f2) references t1 (f1) on delete set null
|
|
) engine=innodb with system versioning;
|
|
insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071));
|
|
delete from t1;
|
|
select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk;
|
|
pk f1 f2 left(f3, 4) check_row_ts(row_start, row_end)
|
|
1 8 8 SHOR HISTORICAL ROW
|
|
2 8 8 LONG HISTORICAL ROW
|
|
drop table t1;
|
|
# Shorter case for clustered index (MDEV-25004)
|
|
create table t1 (
|
|
y int primary key, r int, f int, key (r),
|
|
foreign key (f) references t1 (r) on delete set null)
|
|
with system versioning engine innodb;
|
|
insert into t1 values (1, 6, 6), (2, 6, 6);
|
|
delete from t1;
|
|
select *, check_row_ts(row_start, row_end) from t1 for system_time all;
|
|
y r f check_row_ts(row_start, row_end)
|
|
1 6 6 HISTORICAL ROW
|
|
2 6 6 HISTORICAL ROW
|
|
drop tables t1;
|
|
# Secondary unique index
|
|
create table t1 (
|
|
y int unique null, r int, f int, key (r),
|
|
foreign key (f) references t1 (r) on delete set null)
|
|
with system versioning engine innodb;
|
|
insert into t1 values (1, 6, 6), (2, 6, 6);
|
|
delete from t1;
|
|
select *, check_row_ts(row_start, row_end) from t1 for system_time all;
|
|
y r f check_row_ts(row_start, row_end)
|
|
1 6 6 HISTORICAL ROW
|
|
2 6 6 HISTORICAL ROW
|
|
drop tables t1;
|
|
# Non-unique index cannot be fixed because it does not trigger duplicate error
|
|
create table t1 (
|
|
y int, r int, f int, key (y), key (r),
|
|
foreign key (f) references t1 (r) on delete set null)
|
|
with system versioning engine innodb;
|
|
insert into t1 values (1, 6, 6), (2, 6, 6);
|
|
delete from t1;
|
|
select *, check_row_ts(row_start, row_end) from t1 for system_time all;
|
|
y r f check_row_ts(row_start, row_end)
|
|
1 6 6 HISTORICAL ROW
|
|
2 6 NULL ERROR: row_end == row_start
|
|
2 6 6 HISTORICAL ROW
|
|
drop tables t1;
|
|
#
|
|
# MDEV-21555 Assertion secondary index is out of sync on delete from versioned table
|
|
#
|
|
create table t1 (a int, b int as (a + 1) virtual, key(a)) engine=innodb with system versioning;
|
|
set foreign_key_checks= off;
|
|
insert into t1 (a) values (1), (2);
|
|
alter table t1 add foreign key (b) references t1 (a), algorithm=copy;
|
|
update t1 set a= null where a = 1;
|
|
delete from t1 where a is null;
|
|
set foreign_key_checks= on;
|
|
delete history from t1;
|
|
delete from t1;
|
|
drop table t1;
|
|
#
|
|
# MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT
|
|
# constraint
|
|
#
|
|
create table t0 (pk integer primary key) with system versioning engine=innodb;
|
|
create table t1 (pk integer primary key,
|
|
foreign key(pk) references t0(pk)
|
|
on delete restrict on update cascade) engine=innodb;
|
|
create table t2 (pk integer);
|
|
insert into t0 (pk) values (1);
|
|
insert into t1 (pk) values (1);
|
|
insert into t2 (pk) values (1);
|
|
delete from t0;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
|
|
replace t0 values (1);
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
|
|
select * into outfile 'load_t0' from t0 ;
|
|
load data infile 'load_t0' replace into table t0;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
|
|
delete t0, t2 from t0 join t2;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pk`) REFERENCES `t0` (`pk`) ON UPDATE CASCADE)
|
|
select pk from t0;
|
|
pk
|
|
1
|
|
# Cleanup
|
|
drop table t1, t0, t2;
|
|
# create_select for a temporary table didn't set up pos_in_locked_tables.
|
|
create table t (a int unique) engine=innodb
|
|
replace select 1 as a, 2 as b union select 1 as a, 3 as c;
|
|
select * from t;
|
|
a b
|
|
1 3
|
|
drop table t;
|
|
create temporary table t (a int unique) engine=innodb
|
|
replace select 1 as a, 2 as b union select 1 as a, 3 as c;
|
|
select * from t;
|
|
a b
|
|
1 3
|
|
drop table t;
|
|
#
|
|
# MDEV-20729 Fix REFERENCES constraint in column definition
|
|
#
|
|
create table t1(id int);
|
|
# system fields can't be foreign keys:
|
|
create or replace table t2(
|
|
x int,
|
|
sys_start SYS_DATATYPE as row start references t1(id),
|
|
sys_end SYS_DATATYPE as row end,
|
|
period for system_time(sys_start, sys_end)
|
|
) engine innodb with system versioning;
|
|
Got one of the listed errors
|
|
create or replace table t2(
|
|
x int,
|
|
sys_start SYS_DATATYPE as row start,
|
|
sys_end SYS_DATATYPE as row end references t1(id),
|
|
period for system_time(sys_start, sys_end)
|
|
) engine innodb with system versioning;
|
|
Got one of the listed errors
|
|
create or replace table t2(
|
|
x int,
|
|
sys_start SYS_DATATYPE as row start,
|
|
sys_end SYS_DATATYPE as row end,
|
|
period for system_time(sys_start, sys_end),
|
|
foreign key (sys_start) references t1(id)
|
|
) engine innodb with system versioning;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
create or replace table t2(
|
|
x int,
|
|
sys_start SYS_DATATYPE as row start,
|
|
sys_end SYS_DATATYPE as row end,
|
|
period for system_time(sys_start, sys_end),
|
|
foreign key (sys_end) references t1(id)
|
|
) engine innodb with system versioning;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
drop table t1;
|
|
# End of 10.5 tests
|