Is the unique constraint supposed to work on a char column?
I wanted to add a constraint such that a combination of two columns
(date_added and hash_key) was always unique but although the
constraint seems to have been added successfully, it seemingly has no
effect:
(please disregard timer measurements, I'm doing lots of concurrent
inserts at the same time)
sql>CREATE TABLE test.keytest (
more> date_added int NOT NULL,
more> hash_key char(32) NOT NULL,
more> dimension1 int NOT NULL,
more> dimension2 int NOT NULL,
more> metric1 int NOT NULL,
more> metric2 int NOT NULL,
more>
more> CONSTRAINT key_test UNIQUE (hash_key,date_added)
more>);
Operation successful
Timer 1855.919 msec 0 rows
sql>
0 tuples
Timer 368.076 msec 0 rows
sql>select * from test.keytest;
0 tuples
Timer 294.551 msec 0 rows
sql>insert into test.keytest (date_added, hash_key, dimension1,
dimension2, metric1, metric2) VALUES (1, a, 1, 1, 2, 2);
!SELECT: identifier 'a' unknown
0 tuples
Timer 22.811 msec 0 rows
sql>insert into test.keytest (date_added, hash_key, dimension1,
dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2);
Rows affected 1
Timer 12.126 msec 0 rows
sql>insert into test.keytest (date_added, hash_key, dimension1,
dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2);
Rows affected 1
Timer 18.345 msec 0 rows
sql>select * from test.keytest;
select * from test.keytest;
+------------+----------------------------------+------------+------------+---------+---------+
| date_added | hash_key | dimension1 |
dimension2 | metric1 | metric2 |
+============+==================================+============+============+=========+=========+
| 1 | a | 1 |
1 | 2 | 2 |
| 1 | a | 1 |
1 | 2 | 2 |
+------------+----------------------------------+------------+------------+---------+---------+
2 tuples
Timer 1.997 msec 2 rows
sql>CREATE UNIQUE INDEX unique_key_test ON test.keytest (date_added, hash_key);
Operation successful
Timer 25.904 msec 2 rows
sql>insert into test.keytest (date_added, hash_key, dimension1,
dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2);
Rows affected 1
Timer 78.637 msec 2 rows
sql>insert into test.keytest (date_added, hash_key, dimension1,
dimension2, metric1, metric2) VALUES (1, 'a', 1, 1, 2, 2);
Rows affected 1
Timer 31.788 msec 2 rows
sql>select * from test.keytest;
select * from test.keytest;
+------------+----------------------------------+------------+------------+---------+---------+
| date_added | hash_key | dimension1 |
dimension2 | metric1 | metric2 |
+============+==================================+============+============+=========+=========+
| 1 | a | 1 |
1 | 2 | 2 |
| 1 | a | 1 |
1 | 2 | 2 |
| 1 | a | 1 |
1 | 2 | 2 |
| 1 | a | 1 |
1 | 2 | 2 |
+------------+----------------------------------+------------+------------+---------+---------+
4 tuples
Timer 18.083 msec 4 rows
I can't seem to get monetdb to refuse to add the rows.
I then tried:
sql>CREATE TABLE test.keytest2 (
more> date_added int NOT NULL,
more> key int NOT NULL,
more> dimension1 int NOT NULL,
more> dimension2 int NOT NULL,
more> metric1 int NOT NULL,
more> metric2 int NOT NULL,
more>
more> CONSTRAINT key_test2 UNIQUE (key,date_added)
more>);
Operation successful
Timer 167.384 msec 0 rows
sql>insert into test.keytest2 (date_added, key, dimension1,
dimension2, metric1, metric2) VALUES (1, 1, 1, 1, 2, 2);
Rows affected 1
Timer 1.693 msec 0 rows
sql>insert into test.keytest2 (date_added, key, dimension1,
dimension2, metric1, metric2) VALUES (1, 1, 1, 1, 2, 2);
!SQLException:assert:INSERT INTO: UNIQUE constraint
'keytest2.key_test2' violated
0 tuples
Timer 66.634 msec 0 rows
sql>select * from test.keytest2;
select * from test.keytest2;
+------------+-------+------------+------------+---------+---------+
| date_added | key | dimension1 | dimension2 | metric1 | metric2 |
+============+=======+============+============+=========+=========+
| 1 | 1 | 1 | 1 | 2 | 2 |
+------------+-------+------------+------------+---------+---------+
1 tuple
Timer 140.707 msec 1 rows
Which worked as expected.
Is this a bug or is it by design?
Thanks,
Guillaume
