|
create or replace table t1 (a varchar(6), s timestamp, e timestamp, period for p(s,e), unique(a(3), p without overlaps));
|
insert into t1 values ('foo','2012-01-01','2015-12-31'),('foobar','2013-01-01','2014-01-01');
|
|
10.5 69077dea25
|
MariaDB [test]> insert into t1 values ('foo','2012-01-01','2015-12-31'),('foobar','2013-01-01','2014-01-01');
|
Query OK, 2 rows affected (0.021 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> select * from t1;
|
+--------+---------------------+---------------------+
|
| a | s | e |
|
+--------+---------------------+---------------------+
|
| foo | 2012-01-01 00:00:00 | 2015-12-31 00:00:00 |
|
| foobar | 2013-01-01 00:00:00 | 2014-01-01 00:00:00 |
|
+--------+---------------------+---------------------+
|
2 rows in set (0.001 sec)
|
The prefix is non-unique, and there is an obvious overlap in the period, so an error is expected.
With "normal" unique key it works as expected of course:
MariaDB [test]> create or replace table t1 (a varchar(6), s timestamp, e timestamp, period for p(s,e), unique(a(3), s, e));
|
Query OK, 0 rows affected (0.235 sec)
|
|
MariaDB [test]> insert into t1 values ('foo','2012-01-01','2015-12-31'),('foobar','2012-01-01','2015-12-31');
|
ERROR 1062 (23000): Duplicate entry 'foo-2012-01-01 00:00:00-2015-12-31 00:00:00' for key 'a'
|
|