How to have a concatenation of string columns as unique constraint in embedded Firebird

[lonk]

From this question, I have revised by adding a field,ID, as primary key into the table with the following SQL:
create table "AlternativeCode" ("ID" bigint generated by default as identity primary key, "AlternativeCode" varchar(100) not null, "Code1" varchar(2) not null, "Code2" varchar(2) not null, "Code3" varchar(2) not null, constraint "ConcatAll" unique ("AlternativeCode", "Code1", "Code2", "Code3"))
Existing table:

Result of testing:

Why do these following data violate constraint ?

LOCalcBASIC_ViolatingCONSTRAINTInFirebird_ConcatenateAllFields.odb (7.3 KB)

Version: 7.1.6.2.0+
Build ID: 10(Build:2)
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Fedora 34 Workstation Official Edition

[RobertG]

Have tested it with the attached database. Didn’t allow to execute macros and opened the table for input data. I could insert all rows, which gave an error in your screenshot, except the row, which is really a duplicate: A-00 | 00 | 00 | 00. Will have a look at the macro code.

Having a look to the code: You are excluding the input for new content with the same “AlternativeCode”. So you don’t allow with your macro to add another row with A-00.

[Ratslinger]

@lonk

There is an error in the original code given in a previous post. This line:

sSQL = "SELECT ""AlternativeCode"" FROM ""AlternativeCode"" WHERE ""AlternativeCode"" = '" & sSelectedItem & "'"

Should be:

sSQL = "SELECT ""AlternativeCode"" FROM ""AlternativeCode"" WHERE ""AlternativeCode"" = '" & sSelectedItem & "'" & _
   " AND ""Code1"" = '" & x1 & "' AND ""Code2"" = '" & x2 & "' AND ""Code3"" = '" & x3 & "'"

which then produces this result: