[#CORE-6252] UNIQUE CONSTRAINT violation - Firebird RDBMS Issue Tracker

Firebird Core

UNIQUE CONSTRAINT violation

Created: 19/Feb/20 02:32 PM Updated: 25/Feb/20 02:01 AM

Component/s:

Engine

Affects Version/s:

3.0.5

Fix Version/s:

4.0 Beta 2, 3.0.6

Environment:

« Hide

Server Version : Ubuntu 16.04.6 LTS Linux
Firebird Version : LI-V3.0.5.33220 Firebird 3.0

QA Status:

Done successfully

Description

« Hide

Updating the data of a table with non-unique data and the creation of a unique costraint on the same table in the same transaction, does not result in the exception of non-unique records

Example :

SET TERM ^;
/* Creating simple stucture */
DROP TABLE test_table
^

CREATE TABLE test_table(
a integer,
b integer
)
^

COMMIT
^

/* Simple Data*/
INSERT INTO test_table(a, b) VALUES (1,1)
^

INSERT INTO test_table(a, b) VALUES (1,2)
^

COMMIT
^

/* Problem start here */
UPDATE test_table
SET b = -999
^

ALTER TABLE TEST_TABLE
ADD CONSTRAINT UNQ1_TEST_TABLE
UNIQUE (A)
^

COMMIT
^
/* Now test_table have a unique costraint on column a but column a not contains unique value */

All

Comments

Change History

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Carlos H. Cantu added a comment - 19/Feb/20 02:57 PM

I hear this for years: Mixing DML and DDL in the same transaction is ask for trouble :-(
FB should deny such situations since it seems that mixing both operations in the same transaction is not "safe". A better option, of course, would be to fix the problems, but I'm not sure if this is possible.

[ Permalink | « Hide ]

Pavel Zotov added a comment - 19/Feb/20 03:03 PM

This is author's slightly modified script; checked on 2.1.7, 2.5.9, 3.0.6, 4.0.0 - result the same. It will create PK and UNIQUE for table with fully duplicate rows.

set bail on;
shell del c:\temp\tmp4test.fdb 2>nul;
create database 'localhost:c:\temp\tmp4test.fdb';
show version;

set autoddl off; -- [ !! ]

commit;
set echo on;
recreate table test(
a int not null,
b int not null
);

commit;

insert into test(a, b) values (1,1);
insert into test(a, b) values (1,2);

commit;

-------------------------------------------------------
update test set b = 1;
alter table test add constraint test_unq unique (a);
-------------------------------------------------------
commit;

-------------------------------------------------------
update test set a = 1;
alter table test add constraint test_pk primary key (b);
-------------------------------------------------------
commit;

show table test;
show index test;

select * from test;
commit;