Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

UNIQUE CONSTRAINT violation [CORE6252] #6495

Closed
firebird-issue-importer opened this issue Feb 19, 2020 · 7 comments
Closed

UNIQUE CONSTRAINT violation [CORE6252] #6495

firebird-issue-importer opened this issue Feb 19, 2020 · 7 comments

Comments

@firebird-issue-importer

Submitted by: Mattia Rosin (rmattia)

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 */

Commits: 1922f1f 9a25464

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 19, 2020

Modified by: Mattia Rosin (rmattia)

environment: Server Version : Ubuntu 16.04.6 LTS Linux Diesel2 4.15.18-24-pve
Firebird Version : LI-V3.0.5.33220 Firebird 3.0

=>

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 19, 2020

Commented by: @WarmBooter

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.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 19, 2020

Commented by: @pavel-zotov

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;

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 20, 2020

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 24, 2020

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

Fix Version: 3.0.6 [ 10889 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 25, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 25, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants