Debian Codegraph @

mysql-test/suite/versioning/r/foreign.result

raw
1 #################
2 # Test RESTRICT #
3 #################
4 create table parent(
5 id int,
6 KEY_TYPE (id)
7 ) engine innodb;
8 create table child(
9 parent_id int,
10 sys_start SYS_DATATYPE as row start invisible,
11 sys_end SYS_DATATYPE as row end invisible,
12 period for system_time(sys_start, sys_end),
13 foreign key(parent_id) references parent(id)
14 on delete restrict
15 on update restrict
16 ) engine innodb with system versioning;
17 insert into parent values(1);
18 insert into child values(1);
19 delete from parent where id = 1;
20 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`))
21 delete from child where parent_id = 1;
22 delete from parent where id = 1;
23 insert into parent values(1);
24 insert into child values(1);
25 update parent set id=id+1;
26 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`))
27 delete from child;
28 update parent set id=id+1;
29 select * from child for system_time all;
30 parent_id
31 1
32 1
33 drop table child;
34 drop table parent;
35 ##############################################
36 # Test when clustered index is a foreign key #
37 ##############################################
38 create table parent(
39 id int(10) unsigned,
40 KEY_TYPE (id)
41 ) engine innodb;
42 create table child(
43 parent_id int(10) unsigned primary key,
44 sys_start SYS_DATATYPE as row start invisible,
45 sys_end SYS_DATATYPE as row end invisible,
46 period for system_time(sys_start, sys_end),
47 foreign key(parent_id) references parent(id)
48 ) engine innodb with system versioning;
49 insert into parent values(1);
50 insert into child values(1);
51 delete from parent where id = 1;
52 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`))
53 drop table child;
54 drop table parent;
55 ################
56 # Test CASCADE #
57 ################
58 create table parent(
59 id int,
60 KEY_TYPE (id)
61 ) engine innodb;
62 create table child(
63 parent_id int,
64 sys_start SYS_DATATYPE as row start invisible,
65 sys_end SYS_DATATYPE as row end invisible,
66 period for system_time(sys_start, sys_end),
67 foreign key(parent_id) references parent(id)
68 on delete cascade
69 on update cascade
70 ) engine innodb with system versioning;
71 insert into parent values(1);
72 insert into child values(1);
73 delete from parent where id = 1;
74 select * from child;
75 parent_id
76 select * from child for system_time all;
77 parent_id
78 1
79 insert into parent values(1);
80 insert into child values(1);
81 update parent set id = id + 1;
82 select * from child;
83 parent_id
84 2
85 select * from child for system_time all;
86 parent_id
87 1
88 1
89 2
90 drop table child;
91 drop table parent;
92 create or replace table parent (
93 id int,
94 KEY_TYPE(id),
95 sys_start SYS_DATATYPE as row start invisible,
96 sys_end SYS_DATATYPE as row end invisible,
97 period for system_time(sys_start, sys_end)
98 ) with system versioning
99 engine innodb;
100 create or replace table child (
101 x int,
102 parent_id int not null,
103 constraint `parent-fk`
104 foreign key (parent_id) references parent (id)
105 on delete cascade
106 on update restrict
107 )
108 engine innodb;
109 insert into parent (id) values (2);
110 insert into child (x, parent_id) values (2, 2);
111 delete from parent;
112 select * from child;
113 x parent_id
114 drop table child;
115 drop table parent;
116 create or replace table parent (
117 id int,
118 KEY_TYPE(id)
119 )
120 engine innodb;
121 create or replace table child (
122 id int primary key,
123 parent_id int not null,
124 row_start SYS_DATATYPE as row start invisible,
125 row_end SYS_DATATYPE as row end invisible,
126 period for system_time(row_start, row_end),
127 constraint `parent-fk`
128 foreign key (parent_id) references parent (id)
129 on delete cascade
130 on update restrict
131 ) with system versioning
132 engine innodb;
133 insert into parent (id) values (3);
134 insert into child (id, parent_id) values (3, 3);
135 delete from parent;
136 select * from child;
137 id parent_id
138 select *, check_row(row_start, row_end) from child for system_time all;
139 id parent_id check_row(row_start, row_end)
140 3 3 HISTORICAL ROW
141 drop table child;
142 drop table parent;
143 #################
144 # Test SET NULL #
145 #################
146 create table parent(
147 id int,
148 KEY_TYPE (id)
149 ) engine innodb;
150 create or replace table child(
151 parent_id int,
152 sys_start SYS_DATATYPE as row start invisible,
153 sys_end SYS_DATATYPE as row end invisible,
154 period for system_time(sys_start, sys_end),
155 foreign key(parent_id) references parent(id)
156 on delete set null
157 on update set null
158 ) engine innodb with system versioning;
159 insert into parent values(1);
160 insert into child values(1);
161 delete from child;
162 insert into child values(1);
163 delete from parent where id = 1;
164 select * from child;
165 parent_id
166 NULL
167 select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
168 parent_id current_row
169 1 0
170 1 0
171 NULL 1
172 delete from child;
173 insert into parent values(1);
174 insert into child values(1);
175 update parent set id= id + 1;
176 select * from child;
177 parent_id
178 NULL
179 select *, current_row(sys_end) as current_row from child for system_time all order by sys_end;
180 parent_id current_row
181 1 0
182 1 0
183 NULL 0
184 1 0
185 NULL 1
186 drop table child;
187 drop table parent;
188 ###########################
189 # Parent table is foreign #
190 ###########################
191 create or replace table parent(
192 id int,
193 KEY_TYPE (id),
194 sys_start SYS_DATATYPE as row start invisible,
195 sys_end SYS_DATATYPE as row end invisible,
196 period for system_time(sys_start, sys_end)
197 ) engine innodb with system versioning;
198 create or replace table child(
199 parent_id int,
200 foreign key(parent_id) references parent(id)
201 ) engine innodb;
202 insert into parent values(1);
203 insert into child values(1);
204 delete from parent;
205 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`))
206 update parent set id=2;
207 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`))
208 delete from child;
209 delete from parent;
210 insert into child values(1);
211 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`))
212 insert into parent values(1);
213 insert into child values(1);
214 delete from parent;
215 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`))
216 update parent set id=2;
217 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`))
218 drop table child;
219 drop table parent;
220 ###################
221 # crash on DELETE #
222 ###################
223 create or replace table a (
224 cola int(10),
225 KEY_TYPE (cola),
226 v_cola int(10) as (cola mod 10) virtual,
227 sys_start SYS_DATATYPE as row start invisible,
228 sys_end SYS_DATATYPE as row end invisible,
229 period for system_time(sys_start, sys_end)
230 ) engine=innodb with system versioning;
231 create index v_cola on a (v_cola);
232 create or replace table b(
233 cola int(10),
234 v_cola int(10),
235 sys_start SYS_DATATYPE as row start invisible,
236 sys_end SYS_DATATYPE as row end invisible,
237 period for system_time(sys_start, sys_end)
238 ) engine=innodb with system versioning;
239 alter table b add constraint `v_cola_fk`
240 foreign key (v_cola) references a (v_cola);
241 insert into a(cola) values (12);
242 insert into b(cola, v_cola) values (10,2);
243 delete from a;
244 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`))
245 drop table b, a;
246 ###############################################
247 # CASCADE UPDATE foreign not system versioned #
248 ###############################################
249 create or replace table parent (
250 id smallint unsigned not null auto_increment,
251 value int unsigned not null,
252 primary key (id, value)
253 ) engine = innodb;
254 create or replace table child (
255 id mediumint unsigned not null auto_increment primary key,
256 parent_id smallint unsigned not null,
257 parent_value int unsigned not null,
258 sys_start SYS_DATATYPE as row start invisible,
259 sys_end SYS_DATATYPE as row end invisible,
260 period for system_time(sys_start, sys_end),
261 constraint `fk_child_parent`
262 foreign key (parent_id, parent_value) references parent (id, value)
263 on delete cascade
264 on update cascade
265 ) engine = innodb with system versioning;
266 create or replace table subchild (
267 id int not null auto_increment primary key,
268 parent_id smallint unsigned not null,
269 parent_value int unsigned not null,
270 constraint `fk_subchild_child_parent`
271 foreign key (parent_id, parent_value) references child (parent_id, parent_value)
272 on delete cascade
273 on update cascade
274 ) engine=innodb;
275 insert into parent (value) values (23);
276 select id, value from parent into @id, @value;
277 insert into child values (default, @id, @value);
278 insert into subchild values (default, @id, @value);
279 select parent_id from subchild;
280 parent_id
281 1
282 update parent set id = 11, value = value + 1;
283 select parent_id from subchild;
284 parent_id
285 11
286 select * from child;
287 id parent_id parent_value
288 1 11 24
289 delete from parent;
290 select count(*) from child;
291 count(*)
292 0
293 select * from child for system_time all;
294 id parent_id parent_value
295 1 1 23
296 1 11 24
297 select count(*) from subchild;
298 count(*)
299 0
300 drop table subchild, child, parent;
301 #
302 # MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation
303 #
304 create or replace table t1 (f1 int, key(f1)) engine=innodb;
305 create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning;
306 set foreign_key_checks= off;
307 insert ignore into t2 values (1);
308 set foreign_key_checks= on;
309 update t2 set f2= 2;
310 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`))
311 delete from t2;
312 drop table t2, t1;
313 #
314 # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation
315 #
316 SET timestamp = 1;
317 SET time_zone='+02:00';
318 SELECT now();
319 now()
320 1970-01-01 02:00:01
321 CREATE TABLE t1 (
322 pk INT UNSIGNED PRIMARY KEY,
323 f1 varchar(255) CHARACTER SET ucs2,
324 f2 longtext CHARACTER SET ucs2,
325 f3 varchar(255),
326 f4 char(255),
327 f5 longtext CHARACTER SET ucs2,
328 f6 INT UNSIGNED,
329 f7 INT UNSIGNED,
330 f8 INT UNSIGNED,
331 f9 INT UNSIGNED,
332 f10 INT UNSIGNED,
333 f11 INT UNSIGNED,
334 f12 varchar(255) CHARACTER SET ucs2,
335 f13 char(255) CHARACTER SET ucs2,
336 f14 char(255) CHARACTER SET ucs2,
337 f15 varchar(255),
338 f16 longtext,
339 f17 char(255)
340 ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
341 INSERT INTO t1 VALUES
342 (1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'),
343 (2, REPEAT('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, REPEAT('o',222), 'oven', 'flower', REPEAT('r',120), 'l', 'g'),
344 (3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'),
345 (4, 'r', 't', REPEAT('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, REPEAT('g',128), 'a', 'w', 'f', 'b', 'b'),
346 (5, 'h', 'y', REPEAT('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', REPEAT('n',149), 'folk', 'd'),
347 (6, 'g', 'violent', REPEAT('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'),
348 (7, 'k', 'uncomfortable', REPEAT('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, REPEAT('j',204), 'j', 'statute', 'emphasis', 'u', 'water'),
349 (8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'),
350 (9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'),
351 (10, REPEAT('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'),
352 (11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility');
353 CREATE TABLE t2 (
354 pk int primary key,
355 f char(255) CHARACTER SET ucs2,
356 key(f)
357 ) ENGINE=InnoDB;
358 INSERT INTO t2 VALUES (1,'against'),(2,'q');
359 SET SQL_MODE= '';
360 SET timestamp = 2;
361 SELECT * INTO OUTFILE 't1.data' FROM t1;
362 SET timestamp = 3;
363 UPDATE t1 SET f13 = 'q';
364 SET timestamp = 4;
365 LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
366 Warnings:
367 Warning 1265 Data truncated for column 'f12' at row 2
368 Warning 1265 Data truncated for column 'f12' at row 4
369 Warning 1265 Data truncated for column 'f12' at row 7
370 SELECT * INTO OUTFILE 't1.data.2' FROM t1;
371 SET timestamp = 5;
372 LOAD DATA INFILE 't1.data.2' REPLACE INTO TABLE t1;
373 Warnings:
374 Warning 1265 Data truncated for column 'f1' at row 2
375 Warning 1265 Data truncated for column 'f12' at row 2
376 Warning 1265 Data truncated for column 'f12' at row 4
377 Warning 1265 Data truncated for column 'f12' at row 7
378 Warning 1265 Data truncated for column 'f1' at row 10
379 SELECT * INTO OUTFILE 't2.data' FROM t2;
380 SET timestamp = 6;
381 LOAD DATA INFILE 't2.data' REPLACE INTO TABLE t2;
382 SET FOREIGN_KEY_CHECKS = OFF;
383 ALTER TABLE t1 ADD FOREIGN KEY (f13) REFERENCES t2 (f) ON DELETE SET NULL;
384 SET timestamp = 7;
385 LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
386 Warnings:
387 Warning 1265 Data truncated for column 'f12' at row 2
388 Warning 1265 Data truncated for column 'f12' at row 4
389 Warning 1265 Data truncated for column 'f12' at row 7
390 SET FOREIGN_KEY_CHECKS = ON;
391 SET SESSION SQL_MODE= 'NO_BACKSLASH_ESCAPES';
392 SET timestamp = 8;
393 LOAD DATA INFILE 't1.data' REPLACE INTO TABLE t1;
394 Warnings:
395 Warning 1265 Data truncated for column 'f12' at row 2
396 Warning 1265 Data truncated for column 'f12' at row 4
397 Warning 1265 Data truncated for column 'f12' at row 7
398 SET timestamp = 9;
399 REPLACE INTO t2 SELECT * FROM t2;
400 DROP TABLE t1, t2;
401 set timestamp= default;
402 set time_zone='+00:00';
403 #
404 # MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation
405 #
406 create or replace table t1 (a int, key(a)) engine innodb with system versioning;
407 create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb;
408 insert into t1 values (1),(2);
409 insert into t2 values (1);
410 # DELETE from referenced table is not allowed
411 delete from t1 where a = 1;
412 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`))
413 drop tables t2, t1;
414 #
415 # MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or server crash in row_ins_foreign_report_err upon DELETE from versioned table with FK
416 #
417 create or replace table t1 (x int primary key) engine innodb;
418 create or replace table t2 (x int, foreign key (x) references t1(x)) engine innodb with system versioning;
419 set foreign_key_checks= off;
420 insert into t2 values (1), (1);
421 set foreign_key_checks= on;
422 # DELETE from foreign table is allowed
423 delete from t2;
424 drop tables t2, t1;
425 create or replace table t1 (a int, key(a)) engine innodb;
426 insert into t1 values (1);
427 create or replace table t2 (b int, foreign key (b) references t1(a)) engine innodb with system versioning;
428 insert into t2 values (1), (1);
429 # DELETE from foreign table is allowed
430 delete from t2;
431 drop tables t2, t1;
432 #
433 # MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table
434 #
435 create table t1 (pk int primary key, f1 int,f2 int, f3 text,
436 key(f1), fulltext(f3), key(f3(10)),
437 foreign key (f2) references t1 (f1) on delete set null
438 ) engine=innodb with system versioning;
439 insert into t1 values (1, 8, 8, 'SHORT'), (2, 8, 8, repeat('LONG', 8071));
440 delete from t1;
441 select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for system_time all order by pk;
442 pk f1 f2 left(f3, 4) check_row_ts(row_start, row_end)
443 1 8 8 SHOR HISTORICAL ROW
444 2 8 8 LONG HISTORICAL ROW
445 drop table t1;
446 # Shorter case for clustered index (MDEV-25004)
447 create table t1 (
448 y int primary key, r int, f int, key (r),
449 foreign key (f) references t1 (r) on delete set null)
450 with system versioning engine innodb;
451 insert into t1 values (1, 6, 6), (2, 6, 6);
452 delete from t1;
453 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
454 y r f check_row_ts(row_start, row_end)
455 1 6 6 HISTORICAL ROW
456 2 6 6 HISTORICAL ROW
457 drop tables t1;
458 # Secondary unique index
459 create table t1 (
460 y int unique null, r int, f int, key (r),
461 foreign key (f) references t1 (r) on delete set null)
462 with system versioning engine innodb;
463 insert into t1 values (1, 6, 6), (2, 6, 6);
464 delete from t1;
465 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
466 y r f check_row_ts(row_start, row_end)
467 1 6 6 HISTORICAL ROW
468 2 6 6 HISTORICAL ROW
469 drop tables t1;
470 # Non-unique index cannot be fixed because it does not trigger duplicate error
471 create table t1 (
472 y int, r int, f int, key (y), key (r),
473 foreign key (f) references t1 (r) on delete set null)
474 with system versioning engine innodb;
475 insert into t1 values (1, 6, 6), (2, 6, 6);
476 delete from t1;
477 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
478 y r f check_row_ts(row_start, row_end)
479 1 6 6 HISTORICAL ROW
480 2 6 NULL ERROR: row_end == row_start
481 2 6 6 HISTORICAL ROW
482 drop tables t1;
483 #
484 # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table
485 #
486 create table t1 (a int, b int as (a + 1) virtual, key(a)) engine=innodb with system versioning;
487 set foreign_key_checks= off;
488 insert into t1 (a) values (1), (2);
489 alter table t1 add foreign key (b) references t1 (a), algorithm=copy;
490 update t1 set a= null where a = 1;
491 delete from t1 where a is null;
492 set foreign_key_checks= on;
493 delete history from t1;
494 delete from t1;
495 drop table t1;
496 #
497 # MDEV-30378 Versioned REPLACE succeeds with ON DELETE RESTRICT
498 # constraint
499 #
500 create table t0 (pk integer primary key) with system versioning engine=innodb;
501 create table t1 (pk integer primary key,
502 foreign key(pk) references t0(pk)
503 on delete restrict on update cascade) engine=innodb;
504 create table t2 (pk integer);
505 insert into t0 (pk) values (1);
506 insert into t1 (pk) values (1);
507 insert into t2 (pk) values (1);
508 delete from t0;
509 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)
510 replace t0 values (1);
511 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)
512 select * into outfile 'load_t0' from t0 ;
513 load data infile 'load_t0' replace into table t0;
514 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)
515 delete t0, t2 from t0 join t2;
516 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)
517 select pk from t0;
518 pk
519 1
520 # Cleanup
521 drop table t1, t0, t2;
522 # create_select for a temporary table didn't set up pos_in_locked_tables.
523 create table t (a int unique) engine=innodb
524 replace select 1 as a, 2 as b union select 1 as a, 3 as c;
525 select * from t;
526 a b
527 1 3
528 drop table t;
529 create temporary table t (a int unique) engine=innodb
530 replace select 1 as a, 2 as b union select 1 as a, 3 as c;
531 select * from t;
532 a b
533 1 3
534 drop table t;
535 #
536 # MDEV-20729 Fix REFERENCES constraint in column definition
537 #
538 create table t1(id int);
539 # system fields can't be foreign keys:
540 create or replace table t2(
541 x int,
542 sys_start SYS_DATATYPE as row start references t1(id),
543 sys_end SYS_DATATYPE as row end,
544 period for system_time(sys_start, sys_end)
545 ) engine innodb with system versioning;
546 Got one of the listed errors
547 create or replace table t2(
548 x int,
549 sys_start SYS_DATATYPE as row start,
550 sys_end SYS_DATATYPE as row end references t1(id),
551 period for system_time(sys_start, sys_end)
552 ) engine innodb with system versioning;
553 Got one of the listed errors
554 create or replace table t2(
555 x int,
556 sys_start SYS_DATATYPE as row start,
557 sys_end SYS_DATATYPE as row end,
558 period for system_time(sys_start, sys_end),
559 foreign key (sys_start) references t1(id)
560 ) engine innodb with system versioning;
561 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
562 create or replace table t2(
563 x int,
564 sys_start SYS_DATATYPE as row start,
565 sys_end SYS_DATATYPE as row end,
566 period for system_time(sys_start, sys_end),
567 foreign key (sys_end) references t1(id)
568 ) engine innodb with system versioning;
569 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
570 drop table t1;
571 # End of 10.5 tests