Bug 3668

Summary: UNIQUE INDEX constraint is not enforced during inserts and updates
Product: SQL Reporter: Martin van Dinther <martin.van.dinther>
Component: allAssignee: SQL devs <bugs-sql>
Status: REOPENED ---    
Severity: enhancement CC: niels
Priority: Normal    
Version: -- development   
Hardware: All   
OS: Linux   

Description Martin van Dinther cwiconfidential 2015-02-05 18:41:56 CET
User-Agent:       Mozilla/5.0 (X11; Linux x86_64; rv:35.0) Gecko/20100101 Firefox/35.0
Build Identifier: 

The uniqueness constraint defined by a
 CREATE UNIQUE INDEX  <idx_nm> ON <tbl_nm> (<col_nm_list>)
is not enforced.
However the
 ALTER TABLE <tbl_nm> ADD CONSTRAINT <uc_nm> UNIQUE (<col_nm_list>)
does check and enforce uniqueness.
The same logic should be implemented to get the expected semantic behavior after creating a unique index.
The good news is that the code to enforce it already exists (as used by unique constraints).

Reproducible: Always

Steps to Reproduce:
1. Start mserver5
2. Start mclient
3. Enter SQL commands:
create table tab (id int not null, nm varchar(30) not null, dt date, qnt dec(30,10), descr text);
insert into tab (id, nm) values (1, 'A');
insert into tab (id, nm, dt, qnt, descr) values (2, 'B', '2015-01-29', 3.1415629, 'iasdfhiasdhagdsnfgankkkjfgjklfgjklsklsklsdfg');
select * from tab;
-- now add some unique indexes
create unique index tab_ui1 on tab (id);
create unique index tab_ui2 on tab (nm);
create unique index tab_ui3 on tab (dt);
create unique index tab_ui4 on tab (qnt);
create unique index tab_ui5 on tab (descr);
create unique index tab_ui6 on tab (id, nm, dt, qnt, descr);
-- check that the indexes exist
select i.table_id, t.name as table_name, i.id as index_id, i.name as index_name, i.type from sys.idxs i join sys.tables t on i.table_id = t.id where t.name = 'tab';

-- test if uniqueness is enforced
insert into tab (id, nm, dt, qnt, descr) select id, nm, dt, qnt, descr from tab;
-- 2 rows inserted !!
select * from tab;
-- 4 rows
-- check for duplicates rows:
select count(*) as count_dupl_rows, id, nm, dt, qnt, descr from tab
group by id, nm, dt, qnt, descr having count(*) > 1
order by id, nm, dt, qnt, descr;
-- 2 rows (id = 1 and 2) exists twice.

Actual Results:  
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.20.0 (unreleased), 'demo'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>create table tab (id int not null, nm varchar(30) not null, dt date, qnt dec(30,10), descr text);
operation successful (2.042ms)
sql>insert into tab (id, nm) values (1, 'A');
1 affected row (1.561ms)
sql>insert into tab (id, nm, dt, qnt, descr) values (2, 'B', '2015-01-29', 3.1415629, 'iasdfhiasdhagdsnfgankkkjfgjklfgjklsklsklsdfg');
1 affected row (1.600ms)
sql>select * from tab;
+------+------+------------+----------------------------------+----------------------------------------------+
| id   | nm   | dt         | qnt                              | descr                                        |
+======+======+============+==================================+==============================================+
|    1 | A    | null       |                             null | null                                         |
|    2 | B    | 2015-01-29 |                     3.1415629000 | iasdfhiasdhagdsnfgankkkjfgjklfgjklsklsklsdfg |
+------+------+------------+----------------------------------+----------------------------------------------+
2 tuples (1.534ms)
sql>create unique index tab_ui1 on tab (id);
operation successful (1.800ms)
sql>create unique index tab_ui2 on tab (nm);
operation successful (1.599ms)
sql>create unique index tab_ui3 on tab (dt);
operation successful (1.618ms)
sql>create unique index tab_ui4 on tab (qnt);
operation successful (1.526ms)
sql>create unique index tab_ui5 on tab (descr);
operation successful (1.611ms)
sql>create unique index tab_ui6 on tab (id, nm, dt, qnt, descr);
operation successful (4.059ms)
sql>select i.table_id, t.name as table_name, i.id as index_id, i.name as index_name, i.type from sys.idxs i join sys.tables t on i.table_id = t.id where t.name = 'tab';
+----------+------------+----------+------------+------+
| table_id | table_name | index_id | index_name | type |
+==========+============+==========+============+======+
|     7280 | tab        |     7282 | tab_ui1    |    0 |
|     7280 | tab        |     7284 | tab_ui2    |    0 |
|     7280 | tab        |     7286 | tab_ui3    |    0 |
|     7280 | tab        |     7288 | tab_ui4    |    0 |
|     7280 | tab        |     7290 | tab_ui5    |    0 |
|     7280 | tab        |     7292 | tab_ui6    |    0 |
+----------+------------+----------+------------+------+
6 tuples (5.228ms)
sql>select * from tab;+------+------+------------+----------------------------------+----------------------------------------------+
| id   | nm   | dt         | qnt                              | descr                                        |
+======+======+============+==================================+==============================================+
|    1 | A    | null       |                             null | null                                         |
|    2 | B    | 2015-01-29 |                     3.1415629000 | iasdfhiasdhagdsnfgankkkjfgjklfgjklsklsklsdfg |
+------+------+------------+----------------------------------+----------------------------------------------+
2 tuples (1.459ms)
sql>insert into tab (id, nm, dt, qnt, descr) select id, nm, dt, qnt, descr from tab;
2 affected rows (2.875ms)
sql>select * from tab;
+------+------+------------+----------------------------------+----------------------------------------------+
| id   | nm   | dt         | qnt                              | descr                                        |
+======+======+============+==================================+==============================================+
|    1 | A    | null       |                             null | null                                         |
|    2 | B    | 2015-01-29 |                     3.1415629000 | iasdfhiasdhagdsnfgankkkjfgjklfgjklsklsklsdfg |
|    1 | A    | null       |                             null | null                                         |
|    2 | B    | 2015-01-29 |                     3.1415629000 | iasdfhiasdhagdsnfgankkkjfgjklfgjklsklsklsdfg |
+------+------+------------+----------------------------------+----------------------------------------------+
4 tuples (0.905ms)
sql>select count(*) as count_dupl_rows, id, nm, dt, qnt, descr from tab
more>group by id, nm, dt, qnt, descr having count(*) > 1
more>order by id, nm, dt, qnt, descr;
+------+------+------+------------+----------------------------------+----------------------------------------+
| coun | id   | nm   | dt         | qnt                              | descr                                  |
: t_du :      :      :            :                                  :                                        :
: pl_r :      :      :            :                                  :                                        :
: ows  :      :      :            :                                  :                                        :
+======+======+======+============+==================================+========================================+
|    2 |    1 | A    | null       |                             null | null                                   |
|    2 |    2 | B    | 2015-01-29 |                     3.1415629000 | iasdfhiasdhagdsnfgankkkjfgjklfgjklskls |
:      :      :      :            :                                  : klsdfg                                 :
+------+------+------+------------+----------------------------------+----------------------------------------+
2 tuples (3.299ms)
sql>


Expected Results:  
the insert should fail with an error message that uniqueness constraint xyz is violated
Comment 1 Niels Nes cwiconfidential 2015-02-08 18:08:11 CET
indices form a basis for checking the constraints, not the other way around.
Comment 2 Martin van Dinther cwiconfidential 2015-02-12 16:33:31 CET
As discussed the semantics of a UNIQUE INDEX should be consistent with other RDBMS behavior as SQL users expect it.

A "quick" implementation could be to generate the ALTER TABLE <tbl_nm> ADD CONSTRAINT <uc_nm> UNIQUE (<col_nm_list>) statement and execute it as part of the CREATE UNIQUE INDEX execution.

Alternatively if we decide to not enforce UNIQUE INDEX constraint, we should issue a warning when the user does a CREATE UNIQUE INDEX ... stating that the UNIQUE keyword is not enforced and the user should use ALTER TABLE <tbl_nm> ADD CONSTRAINT <uc_nm> UNIQUE (<col_nm_list>) instead.