|
create or replace table t1 (a int, key(a)) with system versioning;
|
create or replace table t2 (b int, foreign key (b) references t1(a));
|
insert into t1 values (1),(2);
|
insert into t2 values (1);
|
delete from t1 where a = 1;
|
alter table t1 drop system versioning;
|
MariaDB [test]> delete from t1 where a = 1;
|
Query OK, 1 row affected (0.07 sec)
|
|
MariaDB [test]> select * from t1;
|
+------+
|
| a |
|
+------+
|
| 2 |
|
+------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select * from t2;
|
+------+
|
| b |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select * from t1 for system_time all;
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
+------+
|
2 rows in set (0.00 sec)
|
At this point the result is already questionable – I would think that foreign key constraints shouldn't take into account historical rows; but maybe it's meant to be so by design or by standard. The following, however, is most certainly a problem:
MariaDB [test]> alter table t1 drop system versioning;
|
Query OK, 1 row affected (1.29 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select * from t1;
|
+------+
|
| a |
|
+------+
|
| 2 |
|
+------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select * from t2;
|
+------+
|
| b |
|
+------+
|
| 1 |
|
+------+
|
1 row in set (0.00 sec)
|
So, now we have a non-versioned table t1 and a broken constraint on t2.
|