Hi Bernhard,

programmer@... wrote:

> how to set up a field in a table that is to be used as primary key.
> In MS SQL Server there is the IDENTITY keyword in MySQL there is auto
> increment. What is in InterBase?

there is not a single command in IB that will do this, but maybe this helps:

a) define the table with a suitable field for the primary key:
CREATE TABLE TTEST ( ID Integer, <other fields> );

b) define the primary key column:
ALTER TABLE TTEST ADD CONSTRAINT PK_TTEST PRIMARY KEY ( ID );
(note this will implicitly build a unique index on ID)

c) define the Generator (a "server-side variable" that will store the
current auto-inc value in a multi-user-safe way):
CREATE GENERATOR GEN_ID_TTEST;

d) build a trigger that fires before the record gets inserted and
fills the ID column with the next generator value:

SET TERM ^;

CREATE TRIGGER TRG_ID_TTEST FOR TTEST
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
if ((NEW.ID=0) or (NEW.ID IS NULL)) then /* if it's not already there... */
NEW.ID = GEN_ID(GEN_ID_TTEST, 1); /* ...get the next value */
END
^

a little more sql to code, but you get much more control over what's
happening...

btw. if you're using IB6, define the pk columns as NUMERIC(18,0) - that's
because generators have grown from 32bit (=INTEGER) in IB5.6 to 64bit in
IB6 - unfortunately there is no INT64 sql type in IB6, but NUMERIC(18,0)
*is* actually a 64bit int.

hth,
fingerman