<snip>
You have mentioned in your message the followng:
"Just a tip for the future, don't use superkeys as primary keys.
Create a surrogate value that you fill with a generator, and you avoid
a lot of issues."

Would you be so kind to elaborate a few more details on the above
procedure, and give me some tips where I can find more info about
"...creating surrogate values to fill with the generator...". I am
relatively new to working with Firebird and have no experience in
using the generators.

Thank you once again.

Kind regards
Mariusz

</snip>

A surrogate primary key is simply a primary key field that has no real
world significance, you are free to assign any value to it you like.
The best reason for this is an example. You may be able to identify an
employee by their tax file number. You could use that as the primary
key, but what if the tax office then decides to make the number 5
digits longer, or decides to unify the numbers into more sensible orders?

At best your foreign key cascade rules would have to follow through
every child record and update them when you modify the parent. That is
an unnecessary expense. If you need to extend the field, you will have
to modify all your child tables.

A better approach is to assign every employee an EmployeeID. This is a
number generated by your database that you will never have to change.

Now a generator is the same as a SQL 2003 sequence. It is a thing that
gives you a number. It is multi-user safe, which means that you can be
guaranteed that if you are given a number, you are guaranteed that
no-one else will be given it (providing you don't reset it or
decrement it).

An example of use would be:

---

CREATE GENERATOR GEN_EMPLOYEEID;

CREATE TABLE EMPLOYEE
(
EMPLOYEEID BIGINT,
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50),
DOB DATE,
TAXFILENUMBER VARCHAR(30),
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (UID)
);

COMMIT;

---

You could even declare a unique constraint on TaxFileNumber if applicable.

To get the next value for employeeid,

SELECT GEN_ID(GEN_EMPLOYEEID, 1) FROM RDB$DATABASE;

or, if you want it totally transparent to your application, you can
use a trigger.

---

SET TERM ^ ;

CREATE OR ALTER TRIGGER EMPLOYEE_BI FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.EMPLOYEEID IS NULL) THEN
BEGIN
NEW.EMPLOYEEID = GEN_ID(GEN_EMPLOYEEID, 1);
END
END
^

SET TERM ; ^
COMMIT;

---

That will emulate an autoinc field in other RDBMS.

Adam