--- In firebird-support@yahoogroups.com, Nando Dessena <nando@...> wrote:

>
> Martijn,
>
> M> I know about RDB$DB_KEY -- but I'm not sure if IBExpert uses it.
>
> according to the OP, it appears it doesn't.
>
> M> Either way, the db_key is a trick that only (and not always)

works with

> M> Firebird.
>
> I think it works with all databases supported by IBExpert. :-) And,

in the

> scenario we're talking about (a simple view on a single table's data)
> it works and always has, both in IB and Fb.
>
> M> With any SQL-based database, you work with resultsets (or, in my

example,

> M> a few boxes take from a shelf (= table) and brought to you). If

you do not

> M> have some way to uniquely identify them, you're screwed, db_key

or not.

>
> M> Better have a PK column some way or another.
>
> No question about the above. You argued that IBExpert has no way to

allow

> the user to delete a row in the described case, I just said it does
> and IMHO it should. Not that it matters much to me: I define PK-less
> tables about once in a couple years and I'm not an IBExpert user
> anyway. :-)
>

With respect to the OP, if you define a table without a PK, then that
table should not be anything other than a relationship between two or
many entities. No-one is going to pin you down and demand you change
it, but if you need to be able to identify a given record, it needs a
primary key.

Using Firebird, you can create a generator and a before insert trigger
to generate your PK values so that you do not need to modify anything
in your code.

There is no such thing as a 'record number'. There is also no such
thing as a grid that gets passed between these tools and Firebird.
These tools work by converting changes made inside the grid to insert,
update or delete statements. My guess at their behaviour would be that
if a primary key is defined, the where clause would simply reference
this. If no primary key is defined, it assumes the values of each field.

Others will know better, but I do not think that RDB$DB_KEY is
guaranteed static and may not be suitable for use as some form of
internal record number.

Heck, I will even write the code for you

CREATE TABLE A
(
ID INTEGER NOT NULL,
CONSTRAINT PK_A PRIMARY KEY (ID)
);

CREATE TABLE B
(
ID INTEGER NOT NULL,
CONSTRAINT PK_B PRIMARY KEY (ID)
);

-- Your existing table

CREATE TABLE AB
(
AID INTEGER NOT NULL,
BID INTEGER NOT NULL
);

-- Add database objects.

CREATE GENERATOR GEN_ABID;
ALTER TABLE AB ADD ID INTEGER NOT NULL;
ALTER TABLE AB ADD CONSTRAINT PK_AB PRIMARY KEY (ID);

SET TERM ^ ;

CREATE TRIGGER AB_BIU FOR AB
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(GEN_ABID, 1);
END
END
^

SET TERM ; ^

-- Fix existing records with no PK value

UPDATE AB SET ID=ID WHERE ID IS NULL;

COMMIT;


Adam