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

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 ?
Screenshot from 2021-09-15 22-50-28

LOCalcBASIC_ViolatingCONSTRAINTInFirebird_ConcatenateAllFields.odb (7.3 KB)

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

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.

1 Like


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:

Screenshot at 2021-09-15 11-43-12

1 Like